faster way of executing SELECT … WHERE id IN … in

问题:

I am in the middle of a uni project, when I discovered a huge problem with my database. Using wamp, and a massive (300Mb) database but with just a few tables my queries are very slow :( All tables are created with MyISAM engine. All settings are on default, I am not experienced in any optimisation. I will need to think of some better way to do it, but for now my question is what is the best substitute for the following query:

SELECT * FROM `payments` WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

I can't use left join or any similar solution I have found here, because those IDs (1,2,3,4,5, ...) are not coming from the database. User select the payments he wants to delete, and on the next screen payment details are displayed.

FYI, payments table has more than a million records :)

回答1:

For a continuos range:

SELECT * FROM payments WHERE id BETWEEN 1 AND 10

If the range is disjoint: Create an indexed memory table with the values in it.

CREATE TABLE mem_table (
  pk unsigned integer primary key
) ENGINE = MEMORY;

INSERT INTO mem_table (pk) VALUES (1),(2),...,(10);

SELECT p.* FROM payments p
INNER JOIN mem_table m ON (m.pk = p.id);

See: http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

PS Make sure you have an index on id (this should really be the primary key).