在mysql中执行SELECT ... WHERE ID IN ...的更快方法 [英] faster way of executing SELECT ... WHERE id IN ... in mysql

问题描述

当我发现数据库存在巨大问题时，我正处于uni项目的中间.使用wamp和庞大的数据库(300Mb)，但只有几个表，我的查询非常慢:(所有表都是使用MyISAM引擎创建的.所有设置都是默认设置，我没有任何优化的经验.我需要考虑一下更好的方法，但是现在我的问题是什么是以下查询的最佳替代品:

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);

我不能使用左联接或此处找到的任何类似解决方案，因为这些ID(1、2、3、4、5，...)不是来自数据库的.用户选择要删除的付款，然后在下一个屏幕上显示付款详细信息.

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 :)

推荐答案

对于连续范围:

SELECT * FROM payments WHERE id BETWEEN 1 AND 10

如果范围不相交: 创建一个索引的memory表及其中的值.

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);

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

PS 确保您在id上有一个索引(这实际上应该是主键).

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

这篇关于在mysql中执行SELECT ... WHERE ID IN ...的更快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！