Table A has an index on ‘ObjectId’. We are querying this table using a select similar to this:
SELECT FROM `A` AS a where `a`.`objectId` IN
('00017a41-1ac0-4775-822b-de00cfd5902d', '000216dc-03cb-4e73-8177-3c2e49bc6622', '00035b33-2a62-464a-817b-6e23e8961bce')
In this case a list of three objectids.
But in our real world this list can be over 30.000 items. And in our mysql.slow_log we see something like this:
almost 9.000.000 records examined and almost 32.000 records found. Now if it was using the ObjectId index, I would assume it would not examine so many records. That many sounds to me like a sequential search.
I tried testing this in MySql workbench and using ‘explain’ with a smaller number in the list, it tells me it will use the objectid-index
I cannot query or explain this with a list of 32.000 items, then Workbench crashes. So what is going on, is there a limit on the number in which it will do an indexed search and when it exceeds that number it will do a sequential search? Or is the examined records in the log not what I think it is?
2
Answers
@TSAmerica.com You were right dividing it in smaller chunks solved the problem. Testing showed that when the list is over ~13.000 items, the search became sequential. So we now chop the search in chunks of 10.000.
Create a temporary table containing all the values in the
IN
list, then join with that table.This will use the index in
temp_items
instead of a sequential search. Ifa.objectid
is also indexed, this will be as efficient as MySQL is capable.