I am dealing with MySQL tables that are essentially results of raytracing simulations on a simulated office room with a single venetian blind. I usually need to retrieve the simulation's result for a unique combination of time and blind's settings. So I end up doing a lot of
我正在处理MySQL表,这些表基本上是在带有单个百叶窗的模拟办公室房间进行光线跟踪模拟的结果。我通常需要检索模拟结果,以获得时间和盲目设置的独特组合。所以我最终做了很多
SELECT result FROM results WHERE timestamp='2005-05-05 12:30:25' \
AND opening=40 AND slatangle=60
This looks suspiciously optimizable, since this query should never ever return more than one row. Does it make sense to define an index on the three columns that uniquely identify each row? Or are there other techniques I can use?
这看起来很可疑,因为此查询永远不会返回多行。在三列上定义唯一标识每一行的索引是否有意义?或者我可以使用其他技术吗?
4 个解决方案
#1
3
The answer is most definately a yes. If you define a unique index on timestamp, opening and slatangle MySQL should be able to find your row with very few disc seeks.
答案肯定是肯定的。如果你在时间戳上定义一个唯一索引,打开和slatangle MySQL应该能够找到你的行,只需很少的光盘搜索。
You might experiment with creating an index on timestamp, opening, slateangle and result. MySQL may be able to fetch your data from the index without touching the datafile at all.
您可以尝试在timestamp,opening,slateangle和result上创建索引。 MySQL可能能够从索引中获取数据而根本不接触数据文件。
The MySQL Manual has a section about optimzing queries.
MySQL手册有一个关于优化查询的部分。
#2
3
I would suggest adding LIMIT 1; to the end of the query.
我建议添加LIMIT 1;到查询结束。
William
#3
2
I wouldn't suggest adding 3 indexes. An index using all three columns may be better and even setting the primary key unique on that combination would be best - only if you're sure that it unique.
我不建议添加3个索引。使用所有三列的索引可能更好,甚至在该组合上设置唯一的主键也是最好的 - 只有当您确定它是唯一的时。
#4
2
Yes, create an index of multiple columns helps. Also you should test the performance of different column order, ie O(c1, c2, c3) != O(c2, c1, c3)
是的,创建多列帮助的索引。你也应该测试不同列顺序的性能,即O(c1,c2,c3)!= O(c2,c1,c3)
Have a look
看一看
http://joekuan.wordpress.com/2009/01/23/mysql-optimize-your-query-to-be-more-scalable-part-12/ http://joekuan.wordpress.com/2009/01/23/mysql-optimize-your-query-to-be-more-scalable-part-22/
#1
3
The answer is most definately a yes. If you define a unique index on timestamp, opening and slatangle MySQL should be able to find your row with very few disc seeks.
答案肯定是肯定的。如果你在时间戳上定义一个唯一索引,打开和slatangle MySQL应该能够找到你的行,只需很少的光盘搜索。
You might experiment with creating an index on timestamp, opening, slateangle and result. MySQL may be able to fetch your data from the index without touching the datafile at all.
您可以尝试在timestamp,opening,slateangle和result上创建索引。 MySQL可能能够从索引中获取数据而根本不接触数据文件。
The MySQL Manual has a section about optimzing queries.
MySQL手册有一个关于优化查询的部分。
#2
3
I would suggest adding LIMIT 1; to the end of the query.
我建议添加LIMIT 1;到查询结束。
William
#3
2
I wouldn't suggest adding 3 indexes. An index using all three columns may be better and even setting the primary key unique on that combination would be best - only if you're sure that it unique.
我不建议添加3个索引。使用所有三列的索引可能更好,甚至在该组合上设置唯一的主键也是最好的 - 只有当您确定它是唯一的时。
#4
2
Yes, create an index of multiple columns helps. Also you should test the performance of different column order, ie O(c1, c2, c3) != O(c2, c1, c3)
是的,创建多列帮助的索引。你也应该测试不同列顺序的性能,即O(c1,c2,c3)!= O(c2,c1,c3)
Have a look
看一看
http://joekuan.wordpress.com/2009/01/23/mysql-optimize-your-query-to-be-more-scalable-part-12/ http://joekuan.wordpress.com/2009/01/23/mysql-optimize-your-query-to-be-more-scalable-part-22/