Optimizing query using indexes

Oni

I'm having a hard time optimizing a query of mine. The query below will run once every few seconds so I need to fix it.

lastonline, lastcheck = unix timestamp
status = bool (int) (I'll change the type when finalizing)

SELECT *
FROM list
WHERE lastonline != 0
AND STATUS =0
AND lastcheck < '1262128371'
ORDER BY `lastcheck` ASC
LIMIT 1

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE list ref lastcheck,status,lastonline,status_lastcheck,statu... status_lastcheck 4 const 7822 Using where

I have lastcheck, status, lastonline, status_lastcheck, status_lastonline indexes. I made the status_lastonline index as I thought of making a nested query which includes both lastonline and status. I'm not sure if this would be helpful as:

EXPLAIN SELECT *
FROM list
WHERE lastonline !=0
AND STATUS =0
LIMIT 1

1 SIMPLE list range status,lastonline,status_lastcheck,status_lastonli... status_lastonline 8 NULL 4043 Using where

Also, I don't see why does this needs 4043 rows?

Thanks, and Happy New Year! 😃

Sxooter

If you were using pgsql you could make an index something like:

create index list_lastonline_status_lastcheck on list (lastcheck) where status=0 and lastonline<>0

and probably get pretty good performance.

In MySQL you might try getting rid of indexes in case it's choosing the wrong one. Also see if you've got lots of NULLS in the lastonline field, as <>0 includes all the NULL values. I'm guessing the query planner in MySQL is choosing a bad index to start with, and as MySQL can't bitmap combine indexes together, if it picks the wrong one you get crap performance.

Note that in MySQL the most common way to optimize this is use a temp table to hold the smallest possible dataset (i.e. use the most selective part of the query to build the temp table) and then run the rest of the query on that small dataset.

Kudose

Creating an index on both lastonline and status would help as well

ALTER TABLE list ADD INDEX myIndex (lastonline, STATUS)

Sxooter

Good point on multi-column indexes. Be sure and test it with the fields in both positions as one way may be much faster than the other. The general rule is you want to most selective field first (i.e. the one that will return the least number of results)