MySQL sort too slow for results with many records

I have two tables, the first one is used to group the data of the second with some information about the set, in order to facilitate the pagination of the contents.

Table 1:

 - id              INT(11)    NOT NULL    UNSIGNED    AUTO_INCREMENT
 - date            DATE       NOT NULL
 - total_records   INT(11)    NOT NULL    UNSIGNED
 - created_date    TIMESTAMP
 - updated_date    TIMESTAMP

 - date_index      INDEX      date DESC

Table 2:

 - id              INT(11)    NOT NULL    UNSIGNED    AUTO_INCREMENT
 - order           INT(11)    NOT NULL    UNSIGNED
 - content         LONGTEXT   NOT NULL
 - record_id       INT(11)    NOT NULL    UNSIGNED
 - created_date    TIMESTAMP
 - updated_date    TIMESTAMP

 - content_search  FULLTEXT   content

tabela 2 is related to tabela 1 by record_id field.

The query I'm trying to do is the following:

-- SQL 1
SELECT t1.'date', t2.'id', t2.'order', t2.'content'
FROM 'tabela1' as t1
INNER JOIN 'tabela2' as t2
ON t2.'record_id' = t1.'id'
WHERE MATCH(t1.'content') AGAINST( '"palavras chaves" @5' IN BOOLEAN MODE)
ORDER BY t1.'date'
LIMIT 0, 10;

-- SQL 2
SELECT t1.'date', t2.'id', t2.'order', t2.'content'
FROM 'tabela2' as t2
INNER JOIN 'tabela1' as t1
ON t2.'record_id' = t1.'id'
WHERE MATCH(t1.'content') AGAINST( '"palavras chaves" @5' IN BOOLEAN MODE)
ORDER BY t1.'date'
LIMIT 0, 10;

-- SQL 3
SELECT *
FROM (
    SELECT t1.'date', t2.'id', t2.'order', t2.'content'
    FROM 'tabela2' as t2
    INNER JOIN 'tabela1' as t1
    ON t2.'record_id' = t1.'id'
    WHERE MATCH(t1.'content') AGAINST( '"palavras chaves" @5' IN BOOLEAN MODE)
) as tmp
ORDER BY tmp.'date'
LIMIT 0, 10;

-- SQL 4
SELECT *
FROM (
    SELECT t2.'id', t2.'order', t2.'content', t2.'record_id'
    FROM 'tabela2' as t2
    WHERE MATCH(t1.'content') AGAINST( '"palavras chaves" @5' IN BOOLEAN MODE)
) as tmp
INNER JOIN 'tabela1' as t1
ON tmp.'record_id' = t1.'id'
ORDER BY tmp.'date'
LIMIT 0, 10;

By using EXPLAIN in each, you can see that you are using file-order and temporary tables, that is Using filesort and Using temporary .

For large keywords, with five words for example, the query and sorting is extremely fast, even though few records return. But if you use only one keyword which results in multiple records, the query is quick, but sorting takes too long. In any situation the query is fast, but with the ordering these queries take in average 156 seconds.

I tried to change the tabela 2 denormalizing and putting a copy of the date field to delete the INNER JOIN . The query improved significantly by dropping to an average of 56 seconds in queries with a keyword only. But it still has sorting problems, even creating an index for the date with descending sort.

These problems occur in MySQL 5.6 and 5.7, as I changed the MyISAM engine to InnoDB. MyISAM was not slow and with this change to fetch without ordering they were instantaneous regardless of the number of keywords. In MySQL 5.5 with in MyISAM it was very fast and working perfectly in all situations.

After changing the tabela 2 and putting the date field, my queries were as follows

-- SQL 1
SELECT *
FROM 'tabela2' USE INDEX ('tabela_2_date_desc')
WHERE MATCH ('content') AGAINST ('"palavra" @5' IN BOOLEAN MODE)
LIMIT 0 , 10;

-- SQL 2
SELECT *
FROM 'tabela2'
WHERE MATCH ('content') AGAINST ('"palavra" @5' IN BOOLEAN MODE)
ORDER BY 'date' DESC
LIMIT 0 , 10;

The first query returns instantly but is not sorted, the second query returns with an average of 55 seconds.

Below EXPLAINS

-- SQL 1
1   SIMPLE  tabela2     fulltext    search_content  search_content  0   const   1   100.00  Using where

-- SQL 2
1   SIMPLE  tabela2     fulltext    search_content  search_content  0   const   1   100.00  Using where; Using filesort

Can anyone help me optimize without using another type of bank or search engine like lucene?

I have already tried to take advantage of SQL_CACHE .

% of% has about 1000 records and%% of% of 160,000.