By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,666 Members | 1,909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,666 IT Pros & Developers. It's quick & easy.

indexing fields in the where clause

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
"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.