From: Neil Tompkins
Date: October 7 2011 6:58pm
Subject: Re: MySQL Indexes
List-Archive: http://lists.mysql.com/mysql/225964
Message-Id: <3DA66D96-2BD9-4796-BC41-F042294C3B7C@googlemail.com>
MIME-Version: 1.0 (iPhone Mail 8L1)
Content-Type: multipart/alternative;
	boundary=Apple-Mail-3-404285938
Content-Transfer-Encoding: 7bit

--Apple-Mail-3-404285938
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Do you have any good documentation with regards creating indexes. Also infor=
mation for explain statement and what would be the desired result of the exp=
lain statement?

On 7 Oct 2011, at 17:10, Michael Dykman <mdykman@stripped> wrote:

> How heavily a given table is queried does not directly affect the index si=
ze, only the number and depth of the indexes.
>=20
> No, it is not that unusual to have the index file bigger.  Just make sure t=
hat every index you have is justified by the queries you are making against t=
he table.
>=20
>  - md
>=20
>=20
> On Fri, Oct 7, 2011 at 4:26 AM, Tompkins Neil <neil.tompkins@stripped=
m> wrote:
> Is it normal practice for a heavily queried MYSQL tables to have a index f=
ile bigger than the data file ?
>=20
>=20
> On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman <mdykman@stripped> wrote:=

> Only one index at a time can be used per query, so neither strategy is opt=
imal.  You need at look at the queries you intend to run against the system a=
nd construct indexes which support them.
>=20
>  - md
>=20
> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins <neil.tompkins@stripped=
m> wrote:
> Maybe that was a bad example.  If the query was name =3D 'Red' what index s=
hould I create ?
>=20
> Should I create a index of all columns used in each query or have a index o=
n individual column ?
>=20
>=20
> On 6 Oct 2011, at 17:28, Michael Dykman <mdykman@stripped> wrote:
>=20
>> For the first query, the obvious index on score will give you optimal res=
ults.
>>=20
>> The second query is founded on this phrase: "Like '%Red%' " and no index w=
ill help you there.  This is an anti-pattern, I am afraid.  The only way you=
r database can satisfy that expression is to test each and every record in t=
he that database (the test itself being expensive as infix finding is iterat=
ive).  Perhaps you should consider this approach instead:
>> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
>>=20
>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil <neil.tompkins@googlemail.=
com> wrote:
>> Hi,
>>=20
>> Can anyone help and offer some advice with regards MySQL indexes.  Basica=
lly
>> we have a number of different tables all of which have the obviously prim=
ary
>> keys.  We then have some queries using JOIN statements that run slowly th=
an
>> we wanted.  How many indexes are recommended per table ?  For example sho=
uld
>> I have a index on all fields that will be used in a WHERE statement ?
>>  Should the indexes be created with multiple fields ?  A example  of two
>> basic queries
>>=20
>> SELECT auto_id, name, score
>> FROM test_table
>> WHERE score > 10
>> ORDER BY score DESC
>>=20
>>=20
>> SELECT auto_id, name, score
>> FROM test_table
>> WHERE score > 10
>> AND name Like '%Red%'
>> ORDER BY score DESC
>>=20
>> How many indexes should be created for these two queries ?
>>=20
>> Thanks,
>> Neil
>>=20
>>=20
>>=20
>> --=20
>>  - michael dykman
>>  - mdykman@stripped
>>=20
>>  May the Source be with you.
>=20
>=20
>=20
> --=20
>  - michael dykman
>  - mdykman@stripped
>=20
>  May the Source be with you.
>=20
>=20
>=20
>=20
> --=20
>  - michael dykman
>  - mdykman@stripped
>=20
>  May the Source be with you.

--Apple-Mail-3-404285938--