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: > I didn't think of that (combinations). You are probably right. Due to my > background, I tend not to think a lot about multi-column indices. > > I would think that you want field with the most possible values first, > then > the next, etc. Is that what you were thinking? > > Regards, > > Jerry Schwartz > Global 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 >> Subject: Re: RE: How to rewrite query >> >> 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 (though I guess I did not mention that!). >> >> stype had 6 values, Is_id had 5, so he may have up to about 30 >> combinations as the first two fields, which should be enough to help a >> lot. >> >> Dan >> >> On 10/17/06, Jerry Schwartz <[EMAIL PROTECTED]> wrote: >> > I would think that with so few possible values for all but >> the ip field, >> > indexing the other fields would accomplish nothing. In fact, I'd be >> > surprised if the optimizer didn't realize that and do a >> sequential read >> > anyways. >> > >> > Regards, >> > >> > Jerry Schwartz >> > Global Information Incorporated >> > 195 Farmington Ave. >> > Farmington, CT 06032 >> > >> > 860.674.8796 / FAX: 860.674.8341 >> > >> > >> > > -----Original Message----- >> > > From: mos [mailto:[EMAIL PROTECTED] >> > > Sent: Tuesday, October 17, 2006 10:46 AM >> > > To: mysql@lists.mysql.com >> > > Subject: Re: How to rewrite query >> > > >> > > At 08:34 AM 10/17/2006, you wrote: >> > > >> > > > Hello, >> > > > >> > > > For the Radius server we're using MySQL cluster and the >> > > following query >> > > > looks too slow: >> > > > >> > > >select ip from ipaddr >> > > > where pool='INTERNET' and stype='S' and ls_id=3 and >> > > allocated is null >> > > > limit 1; >> > > > >> > > > Table ipaddr is small (~6MB, 38000 records). Fields in >> > > WHERE clause have >> > > > few values and no indexes: >> > > > - pool: 2 distinct values; >> > > > - stype: 6 distinct values; >> > > > - ls_id: 5 distinct values; >> > > > - allocated is null for ~30000 of records. >> > > > >> > > > Table type is NDB. If I change it to MEMORY everything >> > > starts to fly. >> > > > >> > > > Of course there are a lot of updates to ipaddr table too. >> > > For every >> > > > select there are 3 updates. But updates are of type "update >> > > something >> > > > where ip=ipaddr" and ipaddr is unique key. >> > > > >> > > > What can cause slowdown in NDB case? Table is small and is >> > > in memory >> > > > (5.0 cluster). Maybe I can rewrite it in some better form >> > > for such case? >> > > > MySQL setting are basically default. I did not find >> something in >> > > > documentation about improving performance of NDB engine tables. >> > > > Maybe increase read_buffer_size which is currently the >> > > default 128k? >> > > > Server has 4GB of memory and runs x86_64 version of >> CentOS4 Linux. >> > > > >> > > > Thanks, >> > > > >> > > > Mindaugas >> > > >> > > Mindaugas, >> > > If your queries are always using those fields, why >> > > not create a >> > > single compound index on those fields? This shouldn't slow >> > > down inserts >> > > that much, and if they do, you could always use delayed inserts. >> > > >> > > Mike >> > > >> > > -- >> > > MySQL General Mailing List >> > > For list archives: http://lists.mysql.com/mysql >> > > To unsubscribe: >> > > http://lists.mysql.com/[EMAIL PROTECTED] >> > > >> > > >> > >> > >> > >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> > >> > >> > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
