mySQL newbie seek help with db indexing


#1

Hi,
I don’t quite understand the syntax for database indexes and/or how the
work to help speed up specific database searches. Can somebody suggest
a set of indexs that would make this set of queries go faster?

Contact Load (0.001406) SELECT * FROM contacts WHERE
(contacts.user_id = 1)
Phone Load (0.003268) SELECT * FROM phones WHERE
(phones.contact_id = 8485)
Email Load (0.000371) SELECT * FROM emails WHERE
(emails.contact_id = 8485)
Note Load (0.019782) SELECT * FROM notes WHERE (notes.contact_id
= 8485)
Group Load (0.000154) SELECT groups.* FROM groups INNER JOIN
groupers ON groups.id = groupers.group_id WHERE ((groupers.contact_id
= 8486))

a Contact has_many phones, emails, notes.
a Contact has_many groups :through groupers

Thanks much, and thanks even more if you include some explanation!

thanks,
jp


#2

On Dec 20, 1:01 am, Jeff P. removed_email_address@domain.invalid
wrote:

Hi,
I don’t quite understand the syntax for database indexes and/or how the
work to help speed up specific database searches. Can somebody suggest
a set of indexs that would make this set of queries go faster?
[…]

Very simple: you can profit by indexing on almost anything you’re
going to be doing a lot of searching on. In this case, that means
that you should consider building indices on your foreign key fields
(such as contacts.user_id), since they’re what you’re searching on in
these queries. But before you do that, you might want to try EXPLAIN
SELECT on the queries you’re interested in, and see what mySQL tells
you about where the bottlenecks are.

Best,

Marnen Laibow-Koser
removed_email_address@domain.invalid
http://www.marnen.org


#3

On 20 Dec 2008, at 07:11, Marnen Laibow-Koser wrote:

[…]

Very simple: you can profit by indexing on almost anything you’re
going to be doing a lot of searching on. In this case, that means
that you should consider building indices on your foreign key fields
(such as contacts.user_id), since they’re what you’re searching on in
these queries. But before you do that, you might want to try EXPLAIN
SELECT on the queries you’re interested in, and see what mySQL tells
you about where the bottlenecks are.

All of the above is good. To explain a little more if you do

SELECT * from contacts where contacts.user_id = 2356

and there is no index then the database must examine every single row
in the contacts table to see if it has the right user_id whereas if it
has an index it can go straight there. (similar to how if you have a
phone book and you’re looking for all entries ‘Michael smith’ you
don’t have to read the entire phone book, you can jump to the
appropriate page very quickly)

Fred


#4

On 20 Dec 2008, at 16:45, Jeff P. wrote:

and there is no index then the database must examine every single row
in the contacts table to see if it has the right user_id whereas if
it
has an index it can go straight there. (similar to how if you have a

Fred

Thanks Marnen and Fred,
Does the same logic completely cover joins
Yup

Fred


#5

Frederick C. wrote:

On 20 Dec 2008, at 07:11, Marnen Laibow-Koser wrote:

[…]

Very simple: you can profit by indexing on almost anything you’re
All of the above is good. To explain a little more if you do

SELECT * from contacts where contacts.user_id = 2356

and there is no index then the database must examine every single row
in the contacts table to see if it has the right user_id whereas if it
has an index it can go straight there. (similar to how if you have a

Fred

Thanks Marnen and Fred,
Does the same logic completely cover joins, or is there something
special to consider for those?

thanks,
jp