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 <[EMAIL PROTECTED]> 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
