Here is my situation. I have a MySQL MyISAM table containing about 4 million records with a total of 13,3 GB of data. The table contains messages received from an external system. Two of the columns in the table keep track of a timestamp and a boolean whether the message is handled or not.
这是我的情况。我有一个MySQL MyISAM表,包含大约400万条记录,共有13,3 GB的数据。该表包含从外部系统接收的消息。表中的两列跟踪时间戳和布尔值是否处理消息。
When using this query:
使用此查询时:
SELECT MIN(timestampCB) FROM webshop_cb_onx_message
The result shows up almost instantly.
结果几乎立即出现。
However, I need to find the earliest timestamp of unhandled messages, like this:
但是,我需要找到未处理消息的最早时间戳,如下所示:
SELECT MIN(timestampCB ) FROM webshop_cb_onx_message WHERE handled = 0
The results of this query show up after about 3 minutes, which is way too slow for the script I'm writing.
此查询的结果在大约3分钟后显示,这对于我正在编写的脚本来说太慢了。
Both columns are individually indexed, not together. However, adding an index to the table would take incredibly long considering the amount of data that is in there already.
两列都是单独索引的,而不是一起索引。但是,考虑到已经存在的数据量,在表中添加索引需要非常长的时间。
Does my problem originate from the fact that both columns are separatly indexed, and if so, does anyone have a solution to my issue other than adding another index?
我的问题是否源于这两个列是分开索引的事实,如果是这样,除了添加另一个索引之外,是否有人能解决我的问题?
1 个解决方案
#1
2
It is commonly recommended that if the selectivity of an index over 20% then a full table scan is preferable over an index access. This would mean it is likely that your index on handled won't actually result in using the index but a full table scan given the selectivity.
通常建议,如果索引的选择性超过20%,那么全表扫描优于索引访问。这意味着您的处理索引可能实际上不会导致使用索引,而是在给定选择性的情况下进行全表扫描。
A composite index of handled, timestampCB may actually improve the performance given its a composite index, even if the selectivity isn't great MySQL would most likely still use it - even if it didn't you could force it's use.
一个处理的复合索引,timestampCB实际上可以提高性能,因为它的复合索引,即使选择性不是很大,MySQL很可能仍然会使用它 - 即使它不能强制它使用它。
#1
2
It is commonly recommended that if the selectivity of an index over 20% then a full table scan is preferable over an index access. This would mean it is likely that your index on handled won't actually result in using the index but a full table scan given the selectivity.
通常建议,如果索引的选择性超过20%,那么全表扫描优于索引访问。这意味着您的处理索引可能实际上不会导致使用索引,而是在给定选择性的情况下进行全表扫描。
A composite index of handled, timestampCB may actually improve the performance given its a composite index, even if the selectivity isn't great MySQL would most likely still use it - even if it didn't you could force it's use.
一个处理的复合索引,timestampCB实际上可以提高性能,因为它的复合索引,即使选择性不是很大,MySQL很可能仍然会使用它 - 即使它不能强制它使用它。