怎么为 MySQL 表添加索引?

本教程将介绍如何为 MySQL 表添加索引?的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

怎么为 MySQL 表添加索引? 教程 第1张

问题描述

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';

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';

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.

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 |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+

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`)

Never compare integer to strings in MySQL. If id is int, remove the quotes.

好了关于怎么为 MySQL 表添加索引?的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。