I have an SQL query that is rather big (joining 3 huge tables) and running too slow. I'm trying to optimize it and ran in a strange observation :
我有一个相当大的SQL查询(加入3个巨大的表)并且运行速度太慢。我正在尝试对其进行优化并进行奇怪的观察:
SELECT board FROM ((foo JOIN bar ON id_bar=bar.id) JOIN baz ON id_baz=baz.id) ORDER BY foo.id DESC LIMIT 1;
+-------+
| board |
+-------+
| 3 |
+-------+
1 row in set (3,99 sec)
board is an int field, there is an index on it. Good. But, now, if I'm selecting an indexed varchar(6) field, I get that slow result :
board是一个int字段,它上面有一个索引。好。但是,现在,如果我选择一个索引的varchar(6)字段,我得到的结果很慢:
SELECT type FROM ((foo JOIN bar ON id_bar=bar.id) JOIN baz ON id_baz=baz.id) ORDER BY foo.id DESC LIMIT 1;
+--------+
| type |
+--------+
| normal |
+--------+
1 row in set (17,76 sec)
How is that possible ? I thought the slow part in a query was in the JOIN / ORDER / GROUP / WHERE parts, not in the actual displaying of results. How can I enhance that query ?
怎么可能?我认为查询中的缓慢部分是在JOIN / ORDER / GROUP / WHERE部分,而不是实际显示结果。如何增强该查询?
1 个解决方案
#1
1
INT is 4 bytes long, VARCHAR(6) can be as much as 12 bytes long (in multibyte encoding). That increases the size of index, and thus increases the time.
INT长度为4个字节,VARCHAR(6)长度可达12个字节(多字节编码)。这增加了索引的大小,从而增加了时间。
One thing you could think about is to change type column into another datatype, namely ENUM. ENUM fields let you store efficiently a value from a limited set of possible values (and type columns often have limited amount of possible values). Because it uses less space to store data, the indices on these columns are also small, and thus faster.
您可以考虑的一件事是将类型列更改为另一种数据类型,即ENUM。 ENUM字段允许您有效地存储来自有限的一组可能值的值(并且类型列通常具有有限数量的可能值)。因为它使用较少的空间来存储数据,所以这些列上的索引也很小,因此更快。
#1
1
INT is 4 bytes long, VARCHAR(6) can be as much as 12 bytes long (in multibyte encoding). That increases the size of index, and thus increases the time.
INT长度为4个字节,VARCHAR(6)长度可达12个字节(多字节编码)。这增加了索引的大小,从而增加了时间。
One thing you could think about is to change type column into another datatype, namely ENUM. ENUM fields let you store efficiently a value from a limited set of possible values (and type columns often have limited amount of possible values). Because it uses less space to store data, the indices on these columns are also small, and thus faster.
您可以考虑的一件事是将类型列更改为另一种数据类型,即ENUM。 ENUM字段允许您有效地存储来自有限的一组可能值的值(并且类型列通常具有有限数量的可能值)。因为它使用较少的空间来存储数据,所以这些列上的索引也很小,因此更快。