indexing fields in the where clause

[steve]

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

[Chris Hope]

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/

[steve]

"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