Re: RE: How to rewrite query

[Dan Buettner]

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
>
>