It is important to know and understand that while indexing columns in MySQL will generally improve performance, using the appropriate type of index can make a greater impact on performance.
There are four general index types to consider when creating an appropriate index to optimize SQL queries.
- Column Index
- Concatenated Index
- Covering Index
- Partial Index
For the purpose of this discussion I am excluding other specialized index types such as fulltext, spatial and hash in memory engine.
Example Table
For the following examples, I will use this test table structure.
DROP TABLE IF EXISTS t1; CREATE TABLE t1( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_name VARCHAR(20) NOT NULL, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, external_id INT UNSIGNED NOT NULL, country_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY(id) ) …[Read more]