Question:
I have two tables, the first 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 the record_id field.
The queries I'm trying to do are 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;
Using an EXPLAIN on each one you can see that it is using file sort and temporary tables, ie Using filesort and Using temporary .
For large keywords, with five words for example, the query and sorting is extremely fast, even because few records are returned. But if I use only one keyword which results in several records, the query is fast, but the sort takes too long. In any situation the query is fast, but with sorting these queries take an average of 156 seconds.
I tried to change tabela 2 by denormalizing and putting a copy of the date field to eliminate the INNER JOIN . The query improved significantly dropping to an average of 56 seconds on single keyword queries. But still having problems in sorting, even creating an index for the date with descending sort.
These problems occur in MySQL 5.6 and 5.7 as I changed the engine from MyISAM to InnoDB. MyISAM was in no condition to be slow and with this change the search without sorting became instantaneous regardless of the number of keywords. On MySQL 5.5 with MyISAM it was very fast and working perfectly in all situations.
After changing tabela 2 and putting the date field, my queries looked like this
-- 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 it is not sorted, the second query returns with an average of 55 seconds.
Below the 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 to optimize, without using another type of bank or search engine like lucene?
I've already tried to take advantage of SQL_CACHE .
tabela 1 has about 1000 records and tabela 2 has about 160,000.
Answer:
If you insert data into this table by filling the date column with now() , try sorting table2 by id , which is sequential and will likely obey the date string.
If you have imported these unordered tables, I recommend that you create another table already sorted by date and later revalidate yours for performance analysis.