Jump to content


Photo

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


  • Please log in to reply
2 replies to this topic

#1 mikenl

mikenl
  • Members
  • PipPipPip
  • Advanced Member
  • 38 posts

Posted 03 July 2006 - 04:22 PM

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
	
$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];


#2 tfoster

tfoster
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 03 July 2006 - 04:25 PM

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.

#3 mikenl

mikenl
  • Members
  • PipPipPip
  • Advanced Member
  • 38 posts

Posted 03 July 2006 - 04:49 PM

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...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users