如何向MySQL表添加索引? [英] How do I add indices to MySQL tables?
问题描述
我有一个非常大的MySQL表,大约有150,000行数据。目前,当我尝试并运行时
I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run
SELECT * FROM table WHERE id = '1';
代码运行正常,因为ID字段是主索引。
但是,最近对于项目中的开发,我必须通过另一个字段搜索数据库。例如
the code runs fine as the ID field is the primary index. However, recently for a development in the project, I have to search the database by another field. For example
SELECT * FROM table WHERE product_id = '1';
此字段之前未编入索引,但是,我已将其添加为索引,但是当我试着运行上面的查询,结果很慢。 EXPLAIN查询显示,当我已经添加了一个索引时,product_id字段没有索引,因此查询从20分钟到30分钟的任何位置返回单行。
This field was not previously indexed, however, I've added it as an index, but when I try to run the above query, the results is very slow. An EXPLAIN query reveals that there is no index for the product_id field when I've already added one and as a result the query takes any where from 20 minutes to 30 minutes to return a single row.
我的完整EXPLAIN结果是:
My full EXPLAIN results are:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------+------+---------+------+------+------------------+
| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 157211 | Using where |
+----+-------------+-------+------+----------------------+------+---------+------+------+------------------+
注意我可能会有所帮助我们只看了一眼,ID字段存储为INT,而PRODUCT_ID字段存储为VARCHAR。这可能是问题的根源吗?
It might be helpful to note that I've just taken a look and ID field is stored as INT whereas the PRODUCT_ID field is stored as VARCHAR. Could this be the source of the problem?
推荐答案
ALTER TABLE `table` ADD INDEX `product_id` (`product_id`)
绝不比较整数$ c MySQL中的$ c>到
字符串
。如果 id
是 int
,请删除引号。
Never compare integer
to strings
in MySQL. If id
is int
, remove the quotes.
这篇关于如何向MySQL表添加索引?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!