0 0 0 0


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

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.

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?

Best Answer:

Indexes of two types can be added: when you define a primary key, MySQL will take it as index by default


Primary key as index

Consider you have a TBL_STUDENT table and you want STUDENT_ID as primary key:

ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`)

Above statement adds a primary key, which means that indexed values must be unique and cannot be NULL.

Specify index name

ALTER TABLE `tbl_student` ADD INDEX student_index (`student_id`)

Above statement will create an ordinary index with student_index name.

Create unique index

ALTER TABLE `tbl_student` ADD UNIQUE student_unique_index (`student_id`)

Here, student_unique_index is the index name assigned to STUDENT_ID and creates an index for which values must be unique (here null can be accepted).

Fulltext option

ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`)

Above statement will create the Fulltext index name with student_fulltext_index, for which you need MyISAM Mysql Engine.

How to remove indexes ?

DROP INDEX `student_index` ON `tbl_student`

How to check available indexes?

SHOW INDEX FROM `tbl_student`

Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs