MySQL ODBC Connector - Index and multiple fields

Author

Index and multiple fields

Markus Fischer

2006-03-17, 3:25 am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

is there, performance wise, any difference whether I create one index
for multiple fields or each field with its own index? I'm running 4.0.16.

thx,
- - Markus

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEGnNH1nS0RcIn
K9ARAq9FAJ427uJXMuuj
d6Etnq7fhTSOqmISKgCg
2Tn4
Qpytyz4PD4CPGSMEPX4A
BbI=
=cyqe
-----END PGP SIGNATURE-----

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Косов Евгений

2006-03-17, 7:24 am

Hi, Mark!

Of course, it depends on queries you are running.

I beleive you can find all anwers here:
http://dev.mysql.com/doc/refman/5.0/en/indexes.html
http://dev.mysql.com/doc/refman/5.0...mn-indexes.html
http://dev.mysql.com/doc/refman/5.0...ql-indexes.html

Markus Fischer пишет:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> is there, performance wise, any difference whether I create one index
> for multiple fields or each field with its own index? I'm running 4.0.16.
>
> thx,
> - - Markus
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2.1 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFEGnNH1nS0RcIn
K9ARAq9FAJ427uJXMuuj
d6Etnq7fhTSOqmISKgCg
2Tn4
> Qpytyz4PD4CPGSMEPX4A
BbI=
> =cyqe
> -----END PGP SIGNATURE-----
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Markus Fischer

2006-03-17, 7:24 am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

thanks, somehow I wasn't able to find those pages.

Basically, this means if I've a table like this

id1
id2
id3
id4
id5

and I've two different select statements:

select * from ... where id1 = .. and id2 = ..

and the other being

select * from ... where id3 = .. and id4 = ..

I would create two indexes, one for id1/id2 and the other for id3/id4 ,
right?

again, thanks

- - Markus

Косов Евгений wrote:[color=darkred
]
> Hi, Mark!
>
> Of course, it depends on queries you are running.
>
> I beleive you can find all anwers here:
> http://dev.mysql.com/doc/refman/5.0/en/indexes.html
> http://dev.mysql.com/doc/refman/5.0...mn-indexes.html
> http://dev.mysql.com/doc/refman/5.0...ql-indexes.html
>
>
> Markus Fischer пишет:
> Hi,
>
> is there, performance wise, any difference whether I create one index
> for multiple fields or each field with its own index? I'm running 4.0.16.
>
> thx,
> - Markus
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEGo1p1nS0RcIn
K9ARAjOsAJsGmgh1VVI3
RCG1ci7sr2vBKR7VgQCg
pvg8
k3wTpe1bqh7BIHaDGze+
ttY=
=ZqNR
-----END PGP SIGNATURE-----

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jon Drukman

2006-04-03, 1:29 pm

Markus Fischer wrote:
> Basically, this means if I've a table like this
>
> id1
> id2
> id3
> id4
> id5
>
> and I've two different select statements:
>
> select * from ... where id1 = .. and id2 = ..
>
> and the other being
>
> select * from ... where id3 = .. and id4 = ..
>
> I would create two indexes, one for id1/id2 and the other for id3/id4 ,
> right?

yep. remember that mysql can only use one index per table, and that
column order is significant in a multi column index.

so if your index was (id1, id2) then a query with "where id1 = x and id2
= y" would use the index, as would "id1 = x". but plain old "where id2
= x" would not.

-jsd-

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links