Question 313754 in Mysql Question posted in
The official MySQL documentation can be found here.

Is MySql doing a sequential search if the SELECT contains a WHERE clause that searches a huge list

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:

enter image description here

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

enter image description here

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    @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.


  2. 0

    Create a temporary table containing all the values in the IN list, then join with that table.

    CREATE TEMPORARY TABLE temp_items (
    objectid CHAR(36)
);
INSERT INTO temp_items (objectid) VALUES ('00017a41-1ac0-4775-822b-de00cfd5902d'), ('000216dc-03cb-4e73-8177-3c2e49bc6622'), ('00035b33-2a62-464a-817b-6e23e8961bce');
ALTER TABLE temp_items ADD INDEX (objectid);

SELECT a.*
FROM a 
JOIN temp_items USING (objectid);

    This will use the index in temp_items instead of a sequential search. If a.objectid is also indexed, this will be as efficient as MySQL is capable.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search