Some time ago I wrote about MySQL Full Text Search 🙂 Today I had a very interesting experience with one request. In general, the query looks for results that are missing from another table. One basic Select and one sub select in the WHERE part of the query. In general, the skeleton is
SELECT DISTINCT ( `field` ) FROM `table1` WHERE `someID` =44 AND `firsTextField` NOT IN ( SELECT DISTINCT ( `secondTextField` ) FROM `table2` WHERE `otherID` =44 )
Basically a simple query. I wrote it for 30 sec I run it and the machine loops. After a long and patient wait on my part or more precisely ~ 43 sec . I spat out the result lol . Pffff madhouse. I enter the machine watching the CPU is normally loaded almost in idle condition. Shock and horror. I run the query again the same result. Fuck WTF. I run explain the query and everything shines – the second field secondTextField is only full text search without index, and there the plate is modest of about 35k lines. Who to read – full text search is not an index. The problem is quickly clear one
ALTER TABLE `links` ADD INDEX ( `linkUrlID` )
And things fell into place Query took 0.0005 sec 😀
Be careful how you put your indexes, the speed of the query depends on them marginally.
p.s In general, I am to blame for the above situation not only because it lacks an index but because it does not use the full text search method 😀