Re: Re: How to rewrite query

[Dan Buettner]

You should strongly consider adding an index on the fields you're
querying against.  Right now, none of the fields in your query are
indexed in the table.

I would try something like this for starters: a multi-column index
against all the fields in the query you showed.  If you have other
queries you run regularly, you might evaluate those to see if a
different field order in the index makes sense.  But I think this may
helpl your problem query immensely:

ALTER TABLE ipaddr
ADD INDEX multi_col_idx (stype, ls_id, pool, allocated);

Another suggestion I have is for you to change either your query
slightly, or your table structure slightly.  Your field ls_id is a
VARCHAR field, but you are querying it like it is a numeric field,
which may be forcing MySQL to do a type conversion on all the rows in
the table.  Either change your query to look for
Is_id = '3'  (note the quotes)
or change the column type to an INT and leave your query as-is.
(you know which will better fit your data)

If you do both of these things, I think it should help a lot.

Best,
Dan


On 10/17/06, Mindaugas <mind@stripped> wrote:
>
> > SHOW CREATE TABLE ipaddr;
>
> CREATE TABLE `ipaddr` (
>   `ip` varchar(15) NOT NULL,
>   `pool` varchar(20) NOT NULL,
>   `stype` varchar(1) NOT NULL,
>   `sclass` varchar(1) NOT NULL,
>   `radserv` varchar(1) NOT NULL,
>   `ls_id` varchar(1) NOT NULL default '0',
>   `allocated` datetime default NULL,
>   `msisdn` varchar(20) default NULL,
>   `imsi` varchar(20) default NULL,
>   `session_id` varchar(30) default NULL,
>   `user_name` varchar(20) default NULL,
>   PRIMARY KEY  (`ip`),
>   UNIQUE KEY `ipaddr_msisdn_idx` (`msisdn`)
> ) ENGINE=NDB DEFAULT CHARSET=latin1
>
> > EXPLAIN  select ip from ipaddr
> >   where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null
> >   limit 1;
>
>
> +----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
> | id | select_type | table  | type | possible_keys | key  | key_len | ref  |
> rows  | Extra       |
>
> +----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
> |  1 | SIMPLE      | ipaddr | ALL  | NULL          | NULL | NULL    | NULL |
> 37896 | Using where |
>
> +----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
>
> > When you say it's too slow, how slow is it?  And how fast when it is a
> > memory table?
>
>   With NDB table during peak loads our scripts often does not get the
> answer. And "mysqladmin proc" always
> shows that query in execution. With MEMORY table most often I see sleeping
> mysql process.
>
>   I thought that after we find free ip we change allocated to not null. So
> at the end "beginning" of table consists of records with "allocated is not
> null". So every query has to pass ~8000 records to find "allocated is null"
> row.
>   Am I right there and how to avoid that?
>
> > Also, which specific version of 5.0 are you on?  5.0.x ... what is x?
>
>   5.0.26-max from mysql RPMs.
>
>   Thanks,
>
>   Mindaugas
>
>