Re: Index

[Quoted] On 19.07.2019 23:25, mmmmvh_at_gmail.com wrote:
>
> 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)?

[Quoted] For this(!) query an index on (name, month) will be the best. Read: this index will give you the fastest query execution.

As others already said, MySQL will use only one index (at least for this type of query, RTFM for details). An index on (name) would be more selective than an index on (month). The combined index on (name, month) will yield only hits for the above query. And it could be used just like an index on (name) alone (the "index prefix" rule, again: RTFM).

There are however drawbacks from this strategy of adding "perfect" indexes for your queries. For one there is a limit of 64 indexes per table. Then indexes need space, both on disk and in memory. And finally indexes need maintenance whenever data is modified (rows inserted/deleted/altered). Received on Sun Jul 21 2019 - 10:35:08 CEST