MySQL maxed out? Slow query prob

[mikenl]

I hope someone can solve this:

I have a query that selects data from 2 tables and counts the rows that are returned. Pretty much standard. I find it very slow, while the main table only has 12.000 rows. What can be done to speed up this query or is MySQL just at its limit here?

SELECT
SQL_CALC_FOUND_ROWS
user_profile.un_id,
user_profile.username,
user_profile.country,
user_profile.state,
user_profile.city_1,
user_profile.profdate,
user_profile.profupdate,
user_profile.img,
online.status
FROM user_profile
LEFT JOIN online ON user_profile.un_id = online.un_id
WHERE user_profile.active = '1'
AND user_profile.img != '0'
ORDER BY user_profile.profdate DESC
LIMIT 0 , 30

==================

Query runs anywhere between:

Showing rows 0 - 29 (11321 total, Query took 1.9654 sec)
Showing rows 0 - 29 (11322 total, Query took 2.3472 sec)

==================

Table ONLINE has on average 100 rows.

==================

EXPLAIN:

1 SIMPLE user_profile ref active active 1 const 6102 Using where; Using filesort
1 SIMPLE online eq_ref PRIMARY PRIMARY 4 user_profile.un_id 1


==================

I have indexes on:

table ONLINE: un_id and status

table USER_PROFILE:

PRIMARY PRIMARY 12213 un_id
username UNIQUE 12213 username
country INDEX 381 country 20
state INDEX 305 state 20
city_1 INDEX 3053 city_1 20
active INDEX 3053 active
                          img
                          profdate
profdate INDEX 2442 profupdate
profdate_2 INDEX 2035 profdate

[fenway]

There are three problems: first, your index of active isn't useful, since the cardinality of this field is probably very low (probably just 2 or 3 different values).  Second, your using a NOT (!=), which means that the img index won't be able to be utilized either. Third, your ORDER BY incurs a filesort, since you're already "used" an index on this table, and MySQL <5 won't use multiple -- which means a multi-column index is required, or you have to work the query.

[mikenl]

Thank you for your clear answer  :)

I have a multi-column index:

active INDEX 3053 active
                          img
                          profdate

For reasons that you mentioned I understand that this index just won't work.

I understand that I will have to change the query or upgrade to MySQL 5.