Jump to content


Photo

Too many database queries load my page too slow


  • Please log in to reply
23 replies to this topic

#21 algarve4me

algarve4me
  • Members
  • PipPip
  • Member
  • 16 posts
  • LocationPortugal

Posted 09 March 2006 - 11:22 AM

[!--quoteo(post=353148:date=Mar 8 2006, 11:59 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 8 2006, 11:59 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Debate?? From who? The insane code-monkey squad? SELECT COUNT(*) will always be faster than returning the whole dataset if you're after a count. Always. No exceptions. Do not pass Go, do not collect $200, do not feed the trolls.
[/quote]

Ok, I have taken your advice on this matter and my queries are using SELECT COUNT(*).

Because I had so many queries, to change all of them would have taken too long and I have found no definate yes for using COUNT.

If you type in mysql_num_rows or select count into google, it comes back with a few comparisons of which I couldn't be certain about the result either way. Who would be the best person to answer this for certain? Do you think that I should I ask MYSQL direct for an definate answer on this?


#22 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 09 March 2006 - 02:05 PM

Look, when you use COUNT(*), MySQL sends one little integer back to you over the internet. It doesn't even have to do a disk read to get that number because all the information it needs is sitting in memory in an index. When you select all the rows, MySQL has to retrieve all the thousands of rows of data from the hard disk, send you that data over the internet, and when they all finally arrive, you use PHP to count how many.

By all that is holy, there is no way in hell that selecting all the rows is faster. In any universe, even the evil one where Spock has a beard. The only time you should EVER use mysql_num_rows is if you need all the data AND a count. You just need a count, so don't use it.

Btw, if you ask a mysql developer about this, he will most likely laugh at you. Hard. So be prepared. :)

#23 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 09 March 2006 - 03:51 PM

[!--quoteo(post=353244:date=Mar 9 2006, 09:05 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 9 2006, 09:05 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Look, when you use COUNT(*), MySQL sends one little integer back to you over the internet. It doesn't even have to do a disk read to get that number because all the information it needs is sitting in memory in an index. When you select all the rows, MySQL has to retrieve all the thousands of rows of data from the hard disk, send you that data over the internet, and when they all finally arrive, you use PHP to count how many.
[/quote]

Agreed.. Although, I'm not 100% sure that the index is always memory resident.. It might have to do a little work, but it's still a LOT less than returning all the rows. If you weren't seeing a significant speed increase (assuming you were asking for hundreds of rows and not just one or 2), then you might need to look a little closer at the code and determine where the bottleneck is. Try adding some timing functions to the code and display the output. That can help indicate where the issues are.

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
By all that is holy, there is no way in hell that selecting all the rows is faster. In any universe, even the evil one where Spock has a beard. The only time you should EVER use mysql_num_rows is if you need all the data AND a count. You just need a count, so don't use it.
[/quote]

Hrm.. I liked spock better with the beard.. :)
--
[a href=\"http://blog.godshell.com\" target=\"_blank\"]XenoPhage[/a]
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Something mysterious is formed, born in the silent void. Waiting alone and unmoving, it is at once still and yet in constant motion. It is the source of all programs. I do not know its name, so I will call it the Tao of Programming.[/quote]

#24 algarve4me

algarve4me
  • Members
  • PipPip
  • Member
  • 16 posts
  • LocationPortugal

Posted 13 July 2006 - 12:59 PM

Ok, My site has been working ok, but I have added error control email notification to my web pages and have now received the following error which relates to the function queries below.

Error Received:-

/homepages/xx/xxxxxxxxx/htdocs/XXXXX/include/page_class.php, Line 636
ERROR(2)
mysql_connect(): Too many connections

Function Queries:-
$this->connection = mysql_connect ($this->server, $this->user, $this->password) or die (mysql_error());
		if (!mysql_select_db("xxxxxxxxxx", $this->connection)) {
			echo "<p>There has been a connection error:<br />
			<strong>" . mysql_error() . "</strong><br />
			Please Notify The Website Administrator with the details";
		}
		//CANARY ISLANDS
		list($ci)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Canary Islands' and rentaltype='holiday rental' and foto='y'"));
		//CYPRUS
		list($cyp)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Cyprus' and rentaltype='holiday rental' and foto='y'"));
		//ENGLAND
		list($eng)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='England' and rentaltype='holiday rental' and foto='y'"));
		//FRANCE
		list($fra)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='France' and rentaltype='holiday rental' and foto='y'"));
		//GREECE
		list($gre)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Greece' and rentaltype='holiday rental' and foto='y'"));
		//IRELAND
		list($irl)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Ireland' and rentaltype='holiday rental' and foto='y'"));
		//ITALY
		list($ity)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Italy' and rentaltype='holiday rental' and foto='y'"));
		//PORTUGAL
		list($pgl)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Portugal' and rentaltype='holiday rental' and foto='y'"));
		//SCOTLAND
		list($scot)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Scotland' and rentaltype='holiday rental' and foto='y'"));
		//SPAIN
		list($spain)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Spain' and rentaltype='holiday rental' and foto='y'"));
		//SWITZERLAND
		list($swiss)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Switzerland' and rentaltype='holiday rental' and foto='y'"));
		//TURKEY
		list($tky)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE country='Turkey' and rentaltype='holiday rental' and foto='y'"));
		//HOLIDAY VILLAS
		list($villas)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM TTT WHERE propertytype='villa' and rentaltype='holiday rental' and foto='y'"));





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users