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?
-James
On Apr 23, 2007, at 5:49 PM, mos wrote:
> James,
> A lot depends on how many rows you are searching on. If
> you only have a couple thousand rows, then a table scan will still
> be fast. If you are searching more rows, say more than 10,000, then
> using the proper index will speed things up. Using a compound index
> is only useful if the user is searching on at least the first field
> of the index. For now, your best bet is to build an index on each
> of the commonly searched columns and MySQL will choose the best
> index for the search.
>
> Mike
>
> At 11:16 AM 4/23/2007, James Tu wrote:
>> I have a table which will be searched via some of the fields in the
>> column.
>>
>> An example of the list of searcheable columns:
>>
>> make
>> model
>> body_color
>> tire_type
>> hub_caps_type
>>
>>
>> The thing is that people might do a search using one or many of the
>> fields as criteria.
>> For example someone might search for :
>> body_color = 1 AND tire_type = 11
>>
>> or just
>> model = 22
>>
>> I read that MySQL only uses one index when it performs a query. I
>> did an EXPLAIN and it appears that only one of the indices is used.
>> What is the proper way to setup indices in this case?
>> Shoud I add an Index for each of these fields OR create a multicolumn
>> index using all of these fields?
>>
>> -James
>>
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?
>> unsub=mos99@stripped
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=jtu@stripped
>
>
