Re: Index
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Sun, 21 Jul 2019 19:41:38 +0100
Message-ID: <qh2bl2$9ii$3_at_dont-email.me>
>
> 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).
>
The plural of index is indices.
Date: Sun, 21 Jul 2019 19:41:38 +0100
Message-ID: <qh2bl2$9ii$3_at_dont-email.me>
[Quoted] On 21/07/2019 09:35, Axel Schwenke wrote:
> 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)?
>
> 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).
>
The plural of index is indices.
-- When plunder becomes a way of life for a group of men in a society, over the course of time they create for themselves a legal system that authorizes it and a moral code that glorifies it. Frédéric BastiatReceived on Sun Jul 21 2019 - 20:41:38 CEST