By Michael


2010-06-09 01:41:23 8 Comments

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?

6 comments

@Hieu Vo 2014-05-22 11:08:56

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

@RN Kushwaha 2015-10-15 06:18:32

Hash converted to btree when I see using show indexes.

@Bhavuk Mathur 2016-04-29 08:39:38

What will be the default from Hash and BTree, if I don't specify any?

@Hieu Vo 2016-05-04 07:40:58

BTree is default

@Hieu Vo 2016-05-04 07:56:57

@RNKushwaha because InnoDB and MyIsam don't support HASH, AFAIK, only Memory and NDB storage engines support it

@Jazzzzzz 2016-10-24 17:36:24

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`

@Antony 2015-08-18 11:15:25

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.

@Sam Berry 2016-09-11 23:49:24

Nice, explicitly naming the index allows for easy reversal.

@Bishwas Mishra 2017-10-18 06:44:25

Can you quote the source of the index should match the order of the query fields ?

@Antony 2017-10-19 09:39:12

Unfortunately it was a colleague ...

@Wrikken 2010-06-09 02:05:03

You say you have an index, the explain says otherwise. However, if you really do, this is how to continue:

If you have an index on the column, and MySQL decides not to use it, it may by because:

  1. There's another index in the query MySQL deems more appropriate to use, and it can use only one. The solution is usually an index spanning multiple columns if their normal method of retrieval is by value of more then one column.
  2. MySQL decides there are to many matching rows, and thinks a tablescan is probably faster. If that isn't the case, sometimes an ANALYZE TABLE helps.
  3. In more complex queries, it decides not to use it based on extremely intelligent thought-out voodoo in the query-plan that for some reason just not fits your current requirements.

In the case of (2) or (3), you could coax MySQL into using the index by index hint sytax, but if you do, be sure run some tests to determine whether it actually improves performance to use the index as you hint it.

@zerkms 2010-06-09 01:47:26

ALTER TABLE `table` ADD INDEX `product_id` (`product_id`)

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

@Michael 2010-06-09 01:56:00

I've already added the index using that exact SQL but it seems that it hasn't been "applied" to the data and the EXPLAIN query shows that there is no index for the field.

@Wrikken 2010-06-09 02:10:47

Care to check the index with a SHOW CREATE TABLE, or have you already done that?

@Timo Huovinen 2013-06-07 12:28:33

Use SHOW INDEXES FROM YOURTABLE dev.mysql.com/doc/refman/5.0/en/show-index.html to check if the indexes have been added

@user12345 2014-09-10 21:09:30

Today I had the exact problem @Michael describes, and the solution was to "Never compare integer to strings in mysql." Thank you.

@Ced 2015-08-12 16:04:26

@Wrikken I added my index and used your command SHOW INDEXES FROM YOURTABLE. It shows up but I don't think it is used while I query my table. Do I have to change the select query when using an index ?

@zerkms 2015-08-12 20:52:28

@Ced just ask another question (you may ping me with my handle in its comments) with: 1. the query itself 2. its explain 3. the show create table 4. some statistics on how much data is in the table, how cardinal is the selection

@Ced 2015-08-12 21:00:51

@zerkms I resolved my problem thanks for your input though. The problem was quiet cool though : I generated 100k values from java to my database and my index was on a column set as DATETIME. The thing is that all the dates where the same! When I put random dates everything was working fine.

@x-yuri 2015-08-14 14:19:13

@zerkms Never compare integer to strings in mysql Why not? Doesn't it automatically convert strings to numbers in this case?

@zerkms 2015-08-14 22:03:22

@x-yuri I have personally seen cases when it casted the wrong operand which caused the full scan. Not to say that it makes no sense.

@x-yuri 2015-08-14 23:16:29

Well, type conversion rules says that shouldn't happen. But I wouldn't vouch for that. As for "makes no sense", sure, if you write the statement yourself. But makes sense when it's generated. Why bother adapting to the type of a variable passed, if one could just convert it to string and let mysql handle it...

@zerkms 2015-08-14 23:29:23

@x-yuri well, I did observe that really long ago, not sure what version was that. Even if you generate it automatically, it makes sense to implement a query generator that respects types.

@Wrikken 2015-08-20 19:13:24

@ced: main reason why an index is not used, is cardinality and MySQL thinking a full table scan would be quicker than using the index, or another index might be better suited. Make sure your cardinalities are updated regularly (ANALYZE TABLE if need be), possibly increase their pages to get a more reliable number (at the expense of longer calculating those). If all that fails, and you are really sure and intimately know your data, the USE / FORCE INDEX are at your disposal, but that means you will be forced to check whether that works better every X amount of time from then on.

@Ced 2015-08-21 01:25:15

@Wrikken I replied above you probably didn't see. Actually the reason it wasn't used (or it might have been used idk) is that all my date field had the same value. I did enter programatically 100k values and I didn't think about that.

@Wrikken 2015-08-23 14:27:05

@Ced: yeah, just a drive-by answer when I saw someone commented on a comment more than 5 years old :) But: debugging this in general: if you would have checked cardinality, you would have seen your index was indeed estimated on only a few different values, making it indeed unappealing to use as in index, which stands to reason given how you filled it. So, the answer was more how to debug this in general rather then this specific case ;)

@Ced 2015-08-23 20:00:24

@Wrikken Yeah I usually check if the user has some reputation point before posting on an old comment to see if they are susceptible to answer. Anyway I'll have to check the carnality thing as I have no idea what that is now. Thanks for the tip.

@pabloferraz 2013-05-03 15:52:07

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

@Abhishek Oza 2014-05-21 10:49:48

In MySQL, if you use ALTER TABLE tbl ADD INDEX (col) instead of ALTER TABLE tbl ADD INDEX col (col), then using ALTER TABLE tbl ADD INDEX (col) more than once will keep adding indices named col_2,col_3,... each time. Whereas using ALTER TABLE tbl ADD INDEX col (col) 2nd time, will give ERROR 1061 (42000): Duplicate key name 'col'.

Related Questions

Sponsored Content

37 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

10 Answered Questions

[SOLVED] Improve INSERT-per-second performance of SQLite?

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

8 Answered Questions

[SOLVED] How does database indexing work?

10 Answered Questions

[SOLVED] Duplicating a MySQL table, indices, and data

  • 2010-07-19 09:53:57
  • xkcd150
  • 433871 View
  • 615 Score
  • 10 Answer
  • Tags:   mysql

1 Answered Questions

[SOLVED] Mysql Index is not working

  • 2014-07-24 04:34:39
  • Abhi
  • 2831 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql indexing

1 Answered Questions

[SOLVED] How to index 1 million rows mySQL table for simple query

5 Answered Questions

[SOLVED] Optimizing my mysql query to use index for sorting

1 Answered Questions

MySQL query optimization JOIN

2 Answered Questions

[SOLVED] MySQL ignores my index

Sponsored Content