Archive

Posts Tagged ‘alter table’

MySql add index alter table example

2014/03/04 Leave a comment

Syntax: ALTER TABLE mytable ADD INDEX idx_a_b (a, b);

This MySQL command will create a very useful combined index for this table:

CREATE TABLE IF NOT EXISTS mytable (
id INT(32) UNSIGNED NOT NULL AUTO_INCREMENT,
a INT(32) UNSIGNED NOT NULL,
b INT(32) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

Using that index, this kind of queries will run faster:

SELECT b FROM mytable WHERE a=’value’;

Because data scan is avoided, thanks to index usage.