問題描述

我有一個非常大的 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 表添加索引?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！