johny packet johny packet - 1 year ago 39
MySQL Question

Fastest way to SELECT record from MySql table that not exist inside other table

I have site with rating forms that's recognizing people with combination on IP and fingerprints based on UserAgent and dozens of properties.

When user rate some product, it send his mark to table rating containing his fingerprint too, which is long string like this:


Then site loads next product to rate,which cannot be the same as previous, so i have following query:

SELECT word FROM words
WHERE word NOT IN (SELECT item FROM rating WHERE fingerprint='$fingerprint')

The problem is absurdly long loading time that is rising by number of records in rating table.

Can you give me some advice for better performance?Thx

Answer Source

Indexes help mysql find data in your tables much faster, much like an index in a book. You can add an index like this:

ALTER TABLE rating ADD INDEX fingerprint_idx(fingerprint);

Edit As Ollie said, other indexes might help as well.

To add a compound index (an index on multiple columns), use this statement:

ALTER TABLE rating ADD INDEX fingerprint_item_idx(fingerprint, item);

To add an index for the words column, use this:

ALTER TABLE words ADD INDEX word_idx(word);