MySQL Forums
Forum List  »  InnoDB

Re: is good have single index an composite index on same field
Posted by: Rick James
Date: September 17, 2014 07:48PM

> KEY `last_name` (last_name),
> KEY `last_first` (last_name,first_name)

The first of these is redundant and should be DROPped. (That is a blanket statement; there are rare exceptions.)

Here's the debate:
* Any WHERE (or ORDER BY) clause that would want to use KEY `last_name` could almost equally well use `last_first`.
* `last_first` is more flexible in that it can handle more WHERE clauses.
* `last_first` occupies more disk space (and cache), but this is rarely a big enough deal to matter. (That's a hint of an edge case.)
* The column first_name is similar in size to the column last_name. (This is a hint of another edge case -- where the second field is really big but the first is really small.)

On to your examples...
LIKE '%A%'
will not use _any_ of those indexes. Why? (Hint: leading wildcard)
LIKE 'A%'
will use such indexes.

WHERE last_name LIKE 'A%' AND first_name like 'A%';
will _not_ use both parts of `last_first`
WHERE last_name = 'A' AND first_name like 'A%';
will use both parts of `last_first`. Why? The first part(s) of the INDEX needs to be tested with '='; only the last part (that is used) can efficiently be used with a 'range' query. ("Range" includes "<", BETWEEN, LIKE w/o leading wild, etc.)

Here's an kludgy case. (I am assuming there are more fields in the table than just last_name, first_name, and id):
INDEX(last_name, first_name, id)
SELECT * FROM table
WHERE last_name LIKE '%A%' AND first_name like '%A%';
-- That index is useless; but...
SELECT t.* FROM
( SELECT id FROM table
WHERE last_name LIKE '%A%' AND first_name like '%A%' -- (same WHERE)
) AS x
JOIN table t USING (id);
-- is likely to be more efficient (for very large tables). Here's why...

The subquery scans just the _index_ BTree because all the fields it needs are in the index. It scans the entire index (because of the leading wild card); but the index is probably much smaller than the "data". It collects the `id`s that are relevant; hopefully that is a small subset. Then it looks up each of those ids in `t`. While this is random fetches into the large table, it still might be efficient.

Is that more than you wanted to know? I've got even more, if you prod me the right way.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: is good have single index an composite index on same field
1008
September 17, 2014 07:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.