In MySQL significant performance improvements can be achieved by the correct use of indexes. It is important to understand different MySQL index implementations and one key improvement on indexes defined on single columns is to use multiple column or more commonly known concatenated indexes.
However it’s also possible to define ineffective indexes. This example shows you how to identify a concatenated index that is ineffective.
CREATE TABLE example ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, a INT UNSIGNED NOT NULL, b INT UNSIGNED NOT NULL, c INT UNSIGNED NOT NULL, d INT UNSIGNED NOT NULL, x VARCHAR(10), y VARCHAR(10), z VARCHAR(10), PRIMARY KEY (id), UNIQUE INDEX (a,b,c,d) ) ENGINE=InnoDB; INSERT INTO example(a,b,c,d) VALUES (1,0,1,1),(1,0,1,2), (1,0,2,3), (1,0,4,5), (2,0,2,1),(2,0,2,2), …[Read more]