List:General Discussion« Previous MessageNext Message »
From:James Tu Date:April 24 2007 3:42pm
Subject:Re: what kind of indices to set up
View as plain text  
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
>
>


Thread
what kind of indices to set upJames Tu23 Apr
  • Re: what kind of indices to set upmos23 Apr
    • Re: what kind of indices to set upJames Tu24 Apr
      • Re: what kind of indices to set upJames Tu24 Apr
        • Re: what kind of indices to set upJoerg Bruehe25 Apr
        • Re: what kind of indices to set upJames Tu26 Apr
          • Re: what kind of indices to set upGerald L. Clark26 Apr
            • Re: what kind of indices to set upBaron Schwartz26 Apr