Quote:
Originally Posted by mike_bike_kite
Try something like :
Code:
SELECT u.username, u.is_online
FROM ajaxim_users u
WHERE u.username = 'admin'
or exists (
select 1
FROM ajaxim_buddylists bud
WHERE bud.buddy = 'berdem'
and bud.user = u.username )
and NOT exists (
select 1
FROM ajaxim_blocklists blk
WHERE blk.user = 'berdem'
and blk.buddy = u.username );
Mike |
Thanks Mike, but after trying your SQL, nothing had changed. Still taking at least 3-4secs to execute the query.
Here are the EXAMINE results for your query.
Code:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | PRIMARY | u | ALL | username | NULL | NULL | NULL | 44170 | Using where
3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const table...
2 | DEPENDENT SUBQUERY | bud | ref | buddy | buddy | 102 | const | 1 | Using where
As you can see, still 44170 records are scanned on table "u" (ajaxim_users table). I'm not good at database performance and indexing, but I think I have to decrease row lookups. There is something wrong with the index and this really effects the performance.