0

I have a table which has about one hundred million rows, and the column 'id' is the primary key, and it is the only key in the table.

I do a query like:

SELECT id,name FROM table WHERE id IN (id1, id2, id3, id4, ..., id1000);

These 1000 ids inside "IN" are actually const integers which are pre-caculated by a program.

But Mysql spends about one minute to do the query every time. It is not slow, but it is incredibly slow. What's wrong with the clause? Thank you very much!

Table definition:

CREATE TABLE mytable
(
    id mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    catid smallint(5) unsigned NOT NULL DEFAULT '0',
    name char(39) NOT NULL,
    originalname varchar(255) NOT NULL,
    thumb varchar(255) NOT NULL DEFAULT '',
    description varchar(255) NOT NULL DEFAULT '',
    status tinyint(2) unsigned NOT NULL DEFAULT '1',
    creationtime int(11) unsigned NOT NULL DEFAULT '0',
    updatetime int(11) unsigned NOT NULL DEFAULT '0',
    score int(11) unsigned NOT NULL
    PRIMARY KEY (id)
)
ENGINE=MyISAM
AUTO_INCREMENT=13074618
DEFAULT CHARSET=utf8
user207421
  • 305,947
  • 44
  • 307
  • 483
callUserFunc
  • 1
  • 3
  • `show create table theTableName` says what, [edit] – Drew Oct 19 '16 at 02:59
  • 1
    Option 1: you're missing a usable index – Levi Oct 19 '16 at 03:02
  • 1
    Option 2: the table's working set doesn't fit in memory & it's having to fetch from disk (up to 1000 times) – Levi Oct 19 '16 at 03:02
  • 1
    Option 3: you're using a myisam table with a lot of writes (or a few big slow writes) that are locking out readers – Levi Oct 19 '16 at 03:03
  • CREATE TABLE `mytable` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `catid` smallint(5) unsigned NOT NULL DEFAULT '0', `name` char(39) NOT NULL, `originalname` varchar(255) NOT NULL, `thumb` varchar(255) NOT NULL DEFAULT '', `description` varchar(255) NOT NULL DEFAULT '', `status` tinyint(2) unsigned NOT NULL DEFAULT '1', `creationtime` int(11) unsigned NOT NULL DEFAULT '0', `updatetime` int(11) unsigned NOT NULL DEFAULT '0', `score` int(11) unsigned NOT NULL PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=13074618 DEFAULT CHARSET=utf8 | – callUserFunc Oct 19 '16 at 03:06
  • with a table size of 100 million rows the full index may not be in memory when this query is run. it depends on what other tables there are and how big those indices are. – Mohamed Yasin Oct 19 '16 at 03:10
  • The database is in a local computer, and is only used by me. So it is definitely not caused by a locking problem. – callUserFunc Oct 19 '16 at 03:13
  • Don't post code in comments. You can see for yourself it's completely illegible. Post it in your answer. – user207421 Oct 19 '16 at 03:32
  • You are using myisam. With `innodb` and a 1000 list `IN` I can get the rows in 3.5 seconds on a dumpy laptop. The same list put into a table and using a join all using indexes retrieves the list in 4.8 seconds. – Drew Oct 19 '16 at 03:36
  • The moral of the story is you are using the wrong db engine. `innodb` has the cbo (cost based optimizer) that you are not using, Had you been using `innodb` with cbo you might enjoy this bliss: [one](http://i.imgur.com/0VwJQFw.jpg) and [two](http://i.imgur.com/PIcBXWq.jpg) – Drew Oct 19 '16 at 03:36
  • See also the question from Red Romanov [Here](http://stackoverflow.com/questions/38113747) same issue wrong engine – Drew Oct 19 '16 at 03:46
  • Thank you for your detailed answers, but my engine is already the MyISAM. If I convert it to the INNODB engine, it will take at least days to complete the conversion. If I want a quick solution, isn't there any method to improve the performance? Thank you. – callUserFunc Oct 19 '16 at 05:36
  • Whoever marked this question as a duplicate has not even made a cursory attempt to read the question: it has nothing to do with FIND_IN_SET, and (at least with 1 million records) it uses the PK already. – Levi Oct 19 '16 at 10:44

1 Answers1

1

After the IN 'list' reaches a certain size, MySQL will swap to a TABLE/INDEX SCAN, this has the possibility of being terribly slow.

You can rewrite the query to use a TEMPORARY TABLE, or JOIN (SELECT UNION), to see if that helps performance.

Consider running EXPLAIN EXTENDED to see what slows it down.

stelar7
  • 336
  • 1
  • 3
  • 14
  • where does it say after the IN reaches a certain size? – Drew Oct 19 '16 at 03:30
  • Sorry, I am in no wise, and don't know how to rewrite the query to use a TEMPORARY TABLE. Could you give a reminder? Thank you. – callUserFunc Oct 19 '16 at 03:36
  • This is not a correct answer btw. If you don't know, please don't post wild guesses. :p – Drew Oct 19 '16 at 04:03
  • From the manual: "[mysql will do a full table scan if] You are comparing indexed columns with constant values and MySQL has calculated (based on the index tree) that the constants cover too large a part of the table and that a table scan would be faster" – Levi Oct 19 '16 at 10:46