Re: RE: RE: How to rewrite query

[Dan Buettner]

Yes, it'd be best to have the values with highest cardinality / most
uniqueness first.

On 10/17/06, William R. Mussatto <mussatto@stripped> wrote:
> 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: [phone]
> >
> >
> >> -----Original Message-----
> >> From: Dan Buettner [mailto:drbuettner@stripped]
> >> Sent: Tuesday, October 17, 2006 12:05 PM
> >> To: Jerry Schwartz
> >> Cc: mos; mysql@stripped
> >> 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 <jschwartz@stripped> 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: [phone]
> >> >
> >> >
> >> > > -----Original Message-----
> >> > > From: mos [mailto:mos99@stripped]
> >> > > Sent: Tuesday, October 17, 2006 10:46 AM
> >> > > To: mysql@stripped
> >> > > 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/mysql?unsub=1
> >> > >
> >> > >
> >> >
> >> >
> >> >
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe:
> >> http://lists.mysql.com/mysql?unsub=1
> >> >
> >> >
> >>
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>