From: Date: October 17 2006 4:46pm
Subject: Re: How to rewrite query
List-Archive: http://lists.mysql.com/mysql/202699
Message-Id: <6.0.0.22.2.20061017094424.1a815878@mail.messagingengine.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

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