Mysql – Read performance when indexing one column vs indexing multiple columns

indexinnodbMySQLperformance

(MySQL, InnoDB)

I have a table that has columns A, B and C. That table will have a lot of rows, let's say a million rows. I have a query that will be executed very often and will search the table by the A column, so I added an index on the A column.

I have another query that will not be executed as often as the first one but still often enough to qualify for a speed increase. That query will search by all the three rows.

My question is, will the second query have any speed increase from the index on column A or would I have to add indexes on all three columns in order to increase read speed? In other words, in a query where I have three WHERE conditions, will MySQL first search by the indexed column? Because if it does, out of those million rows, the first column would filter out maybe 100 or so, and querying those by the other two columns would be somewhat trivial and wouldn't require an index.

Best Answer

The index is a guide to the main table. So your first query will use the Index on column A to identify which rows in the table have the data it wants, then does a key lookup to get the rest of the data and do further filtering if necessary.

So your second query will also use Index A and then do a key lookup and then do further filtering as required.

But you can have just a single index on A,B and C and your first query will still use that index (providing that A is the first column indexed).

So you can have your cake and eat it too. I wouldn't create both indexes, I would just create the one on A,B and C. However, If your table is narrow and only has four or five columns then I would only index on A.

Related Solutions

Mysql – Speed up search across multiple columns

You could try something like this to reduce the ORs. This was validated on SQL Server so you may need to tweak a bit:

SELECT * FROM users 
WHERE active=1
AND first_name  IN ('Bill Gates', 'Bill', 'Gates')
OR last_name    IN ('Bill Gates', 'Bill', 'Gates')
OR workplace    IN ('Bill Gates', 'Bill', 'Gates')

Seems like some data validation is called for overall though.

MySQL, “columns partitioning” when multiple columns are individiually important

You are right that mysql will check only one partition for a specific sender_id, but checks all the partitions for a specific receiver_id, as shown here:

mysql> explain partitions select * from messages where sender_id =5;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | messages | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from messages where receiver_id =5;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | messages | p0,p1,p3   | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

However, there are still benefits to that partitioning, depending on your hardware. When looking in all partitions for a receiver_id, mysql is really performing 3 select statements, one for each partition. It may be able to parallelize these select statements. Additionally, if you index receiver_id, it will be accessing 3, smaller, indicies.

In the end, you just have to do performance testing and see if it is paying off for your use case. Seeing as 100MB fits in RAM pretty easily these days, I wouldn't consider partitioning such a small table unless you have specific reasons to do so.

Best Answer

Related Solutions

Mysql – Speed up search across multiple columns

MySQL, “columns partitioning” when multiple columns are individiually important

Related Question