问题描述
我有一个非常大的 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, for a recent development in the project, I have to search the database by another field. For example:
SELECT * FROM table WHERE product_id = '1';
此字段以前未编入索引；但是，我添加了一个，因此 mysql 现在对该字段进行索引，但是当我尝试运行上述查询时，它运行速度非常慢.EXPLAIN 查询显示，当我已经添加了 product_id 字段时，没有索引，因此查询需要 20 分钟到 30 分钟的任何时间来返回单行.
This field was not previously indexed; however, I've added one, so mysql now indexes the field, but when I try to run the above query, it runs very slowly. 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.
我的完整解释结果是:
| 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_index` (`product_id`)
永远不要在 MySQL 中比较 integer 和 strings.如果 id 是 int，请删除引号.
Never compare integer to strings in MySQL. If id is int, remove the quotes.
这篇关于如何向 MySQL 表添加索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！