Some time ago I wrote about MySQL Full Text Search 🙂 Today I had a very interesting experience with a query. In General, the query is looking for results that are missing another table. A Select a sub osnovne and select in the part WHERE the application. In General, the skeleton and is
SELECT DISTINCT ( `field` ) FROM `table1` WHERE `someID` =44 AND `firsTextField` NOT IN ( SELECT DISTINCT ( `secondTextField` ) FROM `table2` WHERE `otherID` =44 )
In General, a simple request. I wrote it for 30 SEC release her and stuck the machine. After a long and patiently waiting on my part or just ~ 43 sec . Spit my score lol . Pfff Madhouse. Enter in the machine looking CPU is normally loaded almost at idle condition. Shock and awe. Run the query again still the same result. Fuck WTF. Run the query and explain everything I – the second field is only secondTextField full text search No index, and there is a modest tray of about 35 k line. What to read – full text search index is not. It is already clear the problem real quick one
ALTER TABLE `links` ADD INDEX ( `linkUrlID` )
And things turned places Query took 0.0005 sec 😀
Be careful how you put the indices of them depends on your marginal rate of application.
p.s Overall I'm hooked on the upper situation not only because there is no index but because not using full text search method 😀