How do I add indexes to MySQL tables?
Solution 1
ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)
Never compare integer
to strings
in MySQL. If id
is int
, remove the quotes.
Solution 2
ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);
Solution 3
You can use this syntax to add an index and control the kind of index (HASH or BTREE).
create index your_index_name on your_table_name(your_column_name) using HASH;
or
create index your_index_name on your_table_name(your_column_name) using BTREE;
You can learn about differences between BTREE and HASH indexes here: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
Solution 4
Indexes of two types can be added: when you define a primary key, MySQL will take it as index by default.
Explanation
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`
Solution 5
It's worth noting that multiple field indexes can drastically improve your query performance. So in the above example we assume ProductID is the only field to lookup but were the query to say ProductID = 1 AND Category = 7 then a multiple column index helps. This is achieved with the following:
ALTER TABLE `table` ADD INDEX `index_name` (`col1`,`col2`)
Additionally the index should match the order of the query fields. In my extended example the index should be (ProductID,Category) not the other way around.
Michael
Updated on July 20, 2022Comments
-
Michael almost 2 years
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?