CODEWITHSUNDEEP
mysqlsql
Alvarez
Alvarez

Reputation: 1333

In which cases should I create one index for multiple columns instead of a separate index for each column?

In which cases should I create one index for multiple columns instead of separate index for each column?

Upvotes: 1

Views: 68

Answers (3)

Andy Lester
Andy Lester

Reputation: 93805

It doesn't have to be an either/or case. Say you have an ORDERS table:

orderid integer,
orderdate date,
etc...

and an ORDERDETAIL table

orderid integer,
lineno integer,
productID integer,
etc...

You will probably want an index on orderdetail.orderid because you're going to do lookups on that all the time, and the DBMS will use that for verifying referential integrity on your foreign key lookup back to orders. However, you probably will wind up doing a lot of selects that look like:

select *
from orderdetail
where orderid=?
order by lineno

In that case, an index on orderdetail.orderid,orderdetail.lineno will be beneficial.

Upvotes: 0

kannanrbk
kannanrbk

Reputation: 7144

Combined index will help in most of the scenarious . If you have a table like below one .

Consider the below table .

Users

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID        | int(11)      | YES  |     | NULL    |       |
| USER      | varchar(100) | YES  |     | NULL    |       |
| EMAIL_ID  | varchar(200) | YES  |     | NULL    |       |
| MODE      | varchar(50)  | YES  |     | NULL    |       |
| TIMESTAMP | bigint(20)   | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

In this table we are auditing user actions like 'SIGNIN' , 'SIGNUP' , 'CLOSE' . In this case we want to get details more faster from this table. But this will have millions of entries .

Values In my table.

 select * from Users;
+------+----------+-----------------------+--------+---------------+
| ID   | USER     | EMAIL_ID              | MODE   | TIMESTAMP     |
+------+----------+-----------------------+--------+---------------+
|    1 | kannan   | [email protected] | SIGNIN | 1353864896000 |
|    2 | bharathi | bharathikannan.r      | SIGNUP | 1353864934000 |
|    2 | mack     | [email protected]        | SIGNIN | 1353865121000 |
|    2 | david    | [email protected]       | SIGNIN | 1353865130000 |
+------+----------+-----------------------+--------+---------------+

Query :

select EMAIL_ID from Users where TIMESTAMP > 1353864896000;

Explain Output:

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Users | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Look at the explain output

It examines full rows in a table. Because , this query is not executed as range one . We want to create index for TIMESTAMP column .

Explain output after created index for timestamp column .

    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Users | ALL  | t_dx          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Still it's not executes as range query . Because we indexed only TIMESTAMP column . We want to create a combined index of TIMESTAMP,EMAIL_ID to execute this query as range one.

create index t_dx on Users(TIMESTAMP,EMAIL_ID);

Explain output after created combined index .

    +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Users | range | t_dx          | t_dx | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

Now , It executes as range query and look at the no of rows examined it's only scans above the given time criteria . Combined index will help for large tables.

Upvotes: 0

Kosta
Kosta

Reputation: 1867

In cases where you have queries with conditions that include multiple columns. If you add all columns form condition to index. You will speed up execution. Use EXPLAIN command to check execution plan before and after adding indexes. Of course, don't add too many columns especially columns of different types, because there could be no benefit of adding an index.

Upvotes: 2

Related Questions