471,108 Members | 1,263 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

indexing fields in the where clause

To gain performance, do I need to index ALL the fields in the where
clause.

Say we have a query like:
select stuff from table where field1=.. and field2=...

If field1 selection substantially reduces the number of records
selected, do we really need to index field2 as well.

In other words, does the query first subselect from the first argumet,
and then select further from the subset, or does the query do two
independent subselects and then merge them together?

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-indexi...ict156808.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=524834
Jul 20 '05 #1
2 1960
steve wrote:
To gain performance, do I need to index ALL the fields in the where
clause.

Say we have a query like:
select stuff from table where field1=.. and field2=...

If field1 selection substantially reduces the number of records
selected, do we really need to index field2 as well.

In other words, does the query first subselect from the first argumet,
and then select further from the subset, or does the query do two
independent subselects and then merge them together?


MySQL is clever enough to work out which columns have indexes on them and
will use those columns first when selecting the data, and the columns that
do not have indexes afterwards, even if you put the where clause in an
order where the first column listed does not have an index.

You are correct in assuming that if the indexed column does most of the work
then you don't really need to index the second column as well, otherwise
you might well end up with every column in the table indexed! You would
probably only need to have the second column indexed as well if a select
query for the first column returned several hundred or thousands of
records.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 20 '05 #2
"Chris Hope" wrote:
steve wrote:
To gain performance, do I need to index ALL the fields in the where clause.

Say we have a query like:
select stuff from table where field1=.. and field2=...

If field1 selection substantially reduces the number of records
selected, do we really need to index field2 as well.

In other words, does the query first subselect from the first argumet,
and then select further from the subset, or does the query do two
independent subselects and then merge them together?


MySQL is clever enough to work out which columns have indexes on

them and
will use those columns first when selecting the data, and the columns that
do not have indexes afterwards, even if you put the where clause in an order where the first column listed does not have an index.

You are correct in assuming that if the indexed column does most of
the work
then you donít really need to index the second column as well,
otherwise
you might well end up with every column in the table indexed! You
would
probably only need to have the second column indexed as well if a
select
query for the first column returned several hundred or thousands of
records.


Chris, thanks for the quick reply. That is exactly the info I was
looking for. Good to hear that the order of fields in the where
clause does not matter, and mysql can figure out how to optimize.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-indexi...ict156808.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=524872
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Thomas Bartkus | last post: by
2 posts views Thread by Kent.Brooke | last post: by
9 posts views Thread by John Sidney-Woollett | last post: by
6 posts views Thread by Hennie7863 | last post: by
6 posts views Thread by Bethany Holliday | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.