我有table1与1M行在我的数据库。
columns: {id, name, timestamp, tag, r, g, b}
indexes: {primary: id, index: timestamp, index: (tag,r,g,b)}
每一行都有一个标签(一个整数)和一个颜色，由它的组件(r,g,b)保存在不同的列中。我的查询应该是:
SELECT * from table1 WHERE tag=... AND (r>... AND r<... AND g>... AND g<... AND b>... AND b<...) ORDER BY timestamp DESC LIMIT 24;
的问题是，当只有少数记录在数据库中为所选的过滤器(标签和颜色)，查询是非常慢的(15秒)。同样值得注意的是，当我从查询中删除ORDER BY timestamp DESC时，它运行得非常快，即使有一些结果。如何解决问题，使查询快捷?
我不知道你说的"很少"是什么意思，但15秒似乎很长。
你想在(tag, r, g, b)上查询一个索引。
也就是说，这不是一个最优索引;或者更准确地说，它是MySQL中最优的。您真正需要的索引类型是RD-Tree，它针对不同维度的范围进行了优化。主要用例是GIS(地理信息系统)。
然而，我不认为MySQL支持rd - tree作为通用索引类型。希望tag是高度选择性的，上述索引可以很好地工作。
INDEX(tag, timestamp)
可能有所帮助。
一般的问题是，优化器看到两个半有用的索引，但没有足够的线索来选择哪一个。然后它会选择不太有益的那个。
当你对g或b的选择相对狭窄时，添加这些可能会有所帮助:
INDEX(tag, g)
INDEX(tag, b)
不幸的是你有4个"范围"在WHERE子句(timestamp, r, g, b)中，优化器只能使用一个。我把tag放在每个人的前面(包括你现有的(tag, r, g, b)，它不会超过r)。
先做=测试;索引可以以一个范围结束;任何后续的范围测试(在您的例子中是g,b)将在索引中被忽略。