How do I add indices to MySQL tables?

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?

 Answers

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

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

---

you can use this syntax to add 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

---

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`