It depends what you need and how your data looks.
Say you have a table with 4 columns:
col1 has 1000 dictinct values
col2 has 10000 dictinct values
col3 has 100 dictinct values
col4 has 100000 dictinct values
In this case:
select col1,col2 from tbl where col1=... and col2=...
Having a single index based on col1,col2 & col3 is the best option,
since no actual table lookup is needed if you have that index.
in this case:
select * from tbl where col3=... and col1=... and col2=...
it is best to have a seperate index for col1, col2 and col3 - in this
case the column with the smallest amount of distinct values should be
first, thus we start with col3.
The result that needs to be returned (*) cover columns that are outside
the where clause, so having an index on all the fields in the where
clause makes no sense, since a table lookup is needed anyway.
In this case having an index of col1,2,3 combined gives us an index with
a much larger cardinality than the sum of the cardinalities in 3
seperate indexes (one for each column) and thus a slower index lookup.
In some cases, you might find a combined index faster even in this case,
but in my experience that is quite rare.
But... as i said in the beginning, it really does depend on your data
and the queries you use - always use explain to check how mysql
optimizes your queries, and run tests like the ones you already did to
see what works best... sometimes mysql surprises the heck out of me even
tho i've been using it since 95 ;)
André Hänsel wrote:
2. Does it really make sense to have single indexes per column when the
columns are used together in one WHERE clause?
Best regards,
André
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
