That's what Dan (and I) meant. Regards,
Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: William R. Mussatto [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 17, 2006 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: > > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
