I'm not certain how MySQL handles the specific case where some columns
in a record covered by a multi-column index are updated; it may update
the whole index entry, or just part of it, not sure. In any case,
yes, there is some overhead associated with having an index on columns
that get updated.
I agree that individual fields have relatively few possible values -
hopefully, when those are combined in a multi-column index, he will
have a greater number of unique combinations, gaining more out of the
index. That's why I suggested putting stype and Is_id as the first
two fields in the in
6 1:28 PM
> To: mysql@lists.mysql.com
> Subject: RE: RE: How to rewrite query
>
> Would it not be best to have the field with the fewest
> repeats (i.e., the
> closest to unique) first, or is that what you meant.
> Bill
>
> On Tue, October 17, 2006 10:12, Jerry Schwartz said:
ilto:[EMAIL PROTECTED]
>> Sent: Tuesday, October 17, 2006 12:05 PM
>> To: Jerry Schwartz
>> Cc: mos; mysql@lists.mysql.com
>> Subject: Re: RE: How to rewrite query
>>
>> I agree that individual fields have relatively few possible values -
>> hopefully, when
Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
>> -Original Message-
>> From: Dan Buettner [mailto:[EMAIL PROTECTED]
>> Sent: Tuesday, October 17, 2006 12:05 PM
>> To: Jerry Schwartz
>> Cc: mos; mysql@lists.mysql.com
>> Subject: Re
lobal Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: Dan Buettner [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 17, 2006 12:05 PM
> To: Jerry Schwartz
> Cc: mos; mysql@lists.mysql.com
>
I agree that individual fields have relatively few possible values -
hopefully, when those are combined in a multi-column index, he will
have a greater number of unique combinations, gaining more out of the
index. That's why I suggested putting stype and Is_id as the first
two fields in the index
You should strongly consider adding an index on the fields you're
querying against. Right now, none of the fields in your query are
indexed in the table.
I would try something like this for starters: a multi-column index
against all the fields in the query you showed. If you have other
queries