2012-10-23

Create Index for MySQL

Here is an example for how to create index on MySQL database, for speed up your MySQL query in design phase.

For example, here is my table schema:
CREATE TABLE records_table(
    id int unsigned auto_increment not null,
    name varchar(20),
    url varchar(100),
    primary key (id)
)

If your query is:
SELECT * FROM records_table
WHERE name='tofu';
You can simply use single column index:
CREATE INDEX idx_records_table_name ON records_table(name);

If your query related to more than one fields:
 SELECT * FROM records_table
WHERE name='tofu'
AND url='www.home.com';
You can create a multiple column index for speed up query:
 CREATE INDEX idx_records_table_name_and_url ON records_table(name, url);

No comments: