Hi James, all,
James Tu wrote:
What do you guys think of this approach...
Always query on all 5 columns...and then create a multicolumn index
using all 5 columns?
From a database point of view, that would be best -
but I am not sure whether it fits your application and the users.
Some points to note:
1) Currently, only one index is used.
2) It is the optimizer's task to decide which index to use.
3) The optimizer will not use an index whose selectivity is too low.
Example for cars:
If you have an index over the number of wheels and your statement
contains "WHERE wheels = 4", the index will not be used, because its
entry for "4" contains almost all rows. There are too few tricycles in
your data (I assume).
4) If an index is defined over multiple fields, then it can only be used
if the statement provides values for the leading fields.
To decide the best combination of indexes to create, you will need data
(or good guesses) on the WHERE conditions which will be used.
You want to support the most frequent statements, while you want to
avoid creating indexes which will not be used (sufficiently often),
because they need both performance (when you change data) and disk space.
On Apr 24, 2007, at 11:42 AM, James Tu wrote:
Thanks Mike.
So let's say I have in index on each of the columns below...and I do a
search for
make=5
model=2
body_color=7
tire_type=11
hub_caps_type=1
MySQL will only pick one of them right? Let's say it picks make_index.
Then what does it do? Does it scan the entire set of results returned
by make=5 to match the other criteria?
Effectively, yes.
So you want to have an index that has a high selectivity - the better
this result set corresponds to the data you want to see, the more it
will improve your performance.
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
