Re: Query Optimization

[Rick James]

Let's analyze how it will perform the query without a FULLTEXT index.
* The only thing used for filtering is "msg LIKE '%abrupt%'". No index is of any use for this -- it has to scan every msg in every row.
* The table is how big? 5GB?
* That means it has to read every row. Since you more data than RAM (yes?), there will be some disk hits, even if you run the query twice in a row.
* Reading a 5GB from disk takes time.

As it stands, your query will be slow, period.

Adding FULLTEXT _should_ have helped, but you have to change the SELECT, too. Recommend "IN BOOLEAN MODE" so that it does not have to check all the rows, just the ones that "MATCH AGAINST 'abrupt'".

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]
The EXPLAIN may have further clues. (Run it with and without FULLTEXT).