MySQL is way slow! How can I get it to speed up improving this query?

[mikenl]

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?

[code]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

$lookup = $lookup." LIMIT $page_start, $per_page";

$query = mysql_query($lookup);

$result = mysql_query("SELECT FOUND_ROWS()");
       $total = mysql_fetch_row($result);
       $num_rows = $total[0];[/code]

[tfoster]

Instead of calculating the number of rows in a loop, try using:

$numRows = mysql_num_rows($query).  This may speed it up a little.

Hope this helps.

[mikenl]

Actually, mysql_num_rows is slower, SQL_CALC_FOUND_ROWS was made to increase speed if you want to know the number of rows returned...