How do I add indices 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  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

User · Answer

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

User · Answer

ALTER TABLE  table  ADD INDEX  product id index    product id     Never compare integer to strings in MySQL  If id is int  remove the quotes

User · Answer

ALTER TABLE TABLE NAME ADD INDEX  COLUMN NAME

User · Answer

A better option is to add the constraints directly during CREATE TABLE query  assuming you have the information about the tables  CREATE TABLE products      productId INT AUTO INCREMENT PRIMARY KEY      productName varchar 100  not null      categoryId INT NOT NULL      CONSTRAINT fk category     FOREIGN KEY  categoryId       REFERENCES categories categoryId          ON UPDATE CASCADE         ON DELETE CASCADE   ENGINE INNODB

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] How do I add indices to MySQL tables?

Examples related to mysql

Examples related to optimization

Examples related to indexing

Examples related to row