Re: Index

In article <9ca5b5b1-8e66-4604-aa8c-6d222f4ba629_at_googlegroups.com>,  <mmmmvh_at_gmail.com> wrote:
> Good evening.
>
> This query
>
> select * from table where name = "Erick" and month = "10"
>
> Is it faster if I have an index to name and another index to month?
> Is it more convenient to have a single index (name, month)?

If you have separate indexes on `name` and `month`, it will only be able to use one of them. It would usually choose the one that helps the most.

Indexes help the most when they are in columns with more unique values, as the number of rows to check is a lot smaller.

For example, if you have 1000 different names, then using an index on `name` would allow it to select, on average, 1/1000 of the total rows.

But an index just on `month` would require it to check 1/12 of the total rows.

So if you have an index on `name`, that is good, but adding a separate index on `month` would not improve anything.

You could certainly have a single index on (`name`,`month`), which would allow it to select 1/12000 of the total rows, so is probably the most efficient.

Such an index would be used when selecting on just `name`, or when selecting on both `name` and `month`, but not when selecting just on `month` (as `month` is not the first column in the index).

Cheers
Tony

-- 
Tony Mountifield
Work: tony_at_softins.co.uk - http://www.softins.co.uk
Play: tony_at_mountifield.org - http://tony.mountifield.org