How to add indexes to MySQL tables?

Question

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.

EDIT:

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

EDIT2:

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?

Answer 1

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

And never to compare integer to strings in mysql. if id is int = remove the quotes.

Answer 2

You say you have an index, the explain says otherwise. However, if you really do, this it 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.