Jump to content

Archived

This topic is now archived and is closed to further replies.

algarve4me

Too many database queries load my page too slow

Recommended Posts

I have added a new number of rows count for the amount of properties in specific categories on each of the different pages that I have on my web site.
However, this results in there being over 300 queries which have slowed my page load time to a standstill. Is there any way I can modify the queries to speed things up?

On my web page I display the number of results as follows:-

Portugal (359)
villas (103)
1 bedroom villas (35)
2 bedroom villas (68)

The queries to produce the amount of properties listed is as follows:-

[code]
//QUERY THE DATABASE TO OBTAIN THE TOTAL NUMBER OF PROPERTIES IN PORTUGAL
$r=mysql_query(" SELECT * FROM table WHERE country='Portugal' and rentaltype='holiday rental' and foto='y'");
$n_pgl_1=mysql_num_rows($r);

//QUERY THE DATABASE TO OBTAIN THE TOTAL NUMBER OF VILLAS IN PORTUGAL
$r=mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and foto='y'");
$n_pgl_v=mysql_num_rows($r);

//QUERY THE DATABASE TO OBTAIN THE TOTAL NUMBER OF 1 BEDROOM VILLAS IN PORTUGAL $r=mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y'");
$n_pgl_v_1=mysql_num_rows($r);

//QUERY THE DATABASE TO OBTAIN THE TOTAL NUMBER OF 2 BEDROOM VILLAS IN PORTUGAL
$r=mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='2' and foto='y'");
$n_pgl_v_2=mysql_num_rows($r);
[/code]

Any help would be greatly appreciated.

Share this post


Link to post
Share on other sites
XenoPhage,

I didn't have any columns indexed. I have created an index of the following columns using phpmyadmin and it does seem to have speeded up the page loading, but I will need to check over the next few hours that it wasn't due to a slow server problem.

Last night when I tried, the pages were loading fast, but when I load the pages during the daytime, the pages are extremely slow.

Indexed:-

country,
propertytype,
rentaltype,
totalbedrooms,
foto,



I have just gone back to my site and now it is still loading slowly. Over 1 minute to load the page.

Share this post


Link to post
Share on other sites
Im just taking a chance here. Do you need all the columns when you select from table?
If you dont need it you could specify the columns you want in your query.
If you have to many queries maybe you could cut down on them and check the content in a while loop.
Just store more data in the result handle.

Also, where do you have your mysql_close();?
You could try to close and open db after each query maybe.

But an index is probably the best.

Im a noob but if im lucky this could help you.
If not, sorry for pointing out something obvius.

/Lt

Share this post


Link to post
Share on other sites
"Also, where do you have your mysql_close();?
You could try to close and open db after each query maybe."

Bad advice! This will slow things down considerably.

As for the code, this might work for you:

[code]$sql =
"(SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and rentaltype='holiday rental' and foto='y')" .
"UNION" .
"(SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and foto='y')" .
"UNION" .
"(SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y')" .
"UNION" .
"(SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='2' and foto='y')";

$result = mysql_query($sql);

$r = mysql_fetch_assoc($result);
$n_pgl_1=$r['cnt'];

$r = mysql_fetch_assoc($result);
$n_pgl_v=$r['cnt'];

$r = mysql_fetch_assoc($result);
$n_pgl_v_1=$r['cnt'];

$r = mysql_fetch_assoc($result);
$n_pgl_v_2=$r['cnt'];[/code]

Share this post


Link to post
Share on other sites
wickning1,

I tried the example that you provided, but the menu that the counts are displayed in only showed the first query as (0) and the other menu levels as () with the number 0 not displayed.

I think that to change so many queries with a similar query line to what I already have would take me too long.
I am trying to figure out if there is any other way of either looping or changing the queries so that they are simplified.

Lt Llama,
From reading various snippets from the mysql web site and other searches, using mysql_close(); should not be needed as the queries will automatically close themselves after execution.

Share this post


Link to post
Share on other sites
Yeah, I was afraid it might not work perfectly. You can just do each query separately (remove the UNIONs and do 4 queries), and that should be better.

Share this post


Link to post
Share on other sites
If you're querying the same table over and over again, you might find it quicker to simply grab all possible needed columns in one query and create a 'virtual table' in an array.

I don't see enough of your queries to give a proper example, nor do I know the potential data size and corresponding resource drain of trying to throw all possible data into memory. This is simply a thought.

Share this post


Link to post
Share on other sites
I am still no further forward with this. I have combined the query and variable into one. I am not sure that this will speed things up that much, but it does save some of the page size and number of page lines and may make it easier to see a way of cutting the code down or combining queries.

The selection of queries below is a sample extract and these queries are repeated up to around 400 times to obtain different counts to display the amounts of properties in a menu. The menu is xhtml dynamic javascript driven sliding menu from brothercake.com.

The table contains hundreds of columns and I am only using those which are relevant to the specific query.

The code below is in a function in a page class. If I temporarily remove the menu from the class, the page loads extremely quickly so I know that the problem definately lies in the queries.

[code]
$pgl_1=mysql_num_rows(mysql_query(" SELECT * FROM table WHERE country='Portugal' and rentaltype='holiday rental' and foto='y'"));    

$pgl_v=mysql_num_rows(mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and foto='y'"));

$pgl_v_1=mysql_num_rows(mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y'"));

$pgl_v_2=mysql_num_rows(mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='2' and foto='y'"));

$pgl_v_3=mysql_num_rows(mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='3' and foto='y'"));

$pgl_v_4=mysql_num_rows(mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='4' and foto='y'"));

$pgl_v_5=mysql_num_rows(mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='5' and foto='y'"));

$pgl_v_6=mysql_num_rows(mysql_query(" SELECT * FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='6' and foto='y'"));
[/code]

Share this post


Link to post
Share on other sites
You're still not using SELECT COUNT(*). Your approach is fundamentally flawed. You are making mysql return all the data in your database when all you need is one number.

[code]$res = mysql_query("SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and rentaltype='holiday rental' and foto='y'");
$row=mysql_fetch_assoc($res);    
mysql_free_result($res);
$pgl_1 = $row['cnt'];

$res = mysql_query("SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and foto='y'");
$row=mysql_fetch_assoc($res);    
mysql_free_result($res);
$pgl_v=$row['cnt'];

$res = mysql_query("SELECT totalbedrooms as beds, COUNT(*) as cnt FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms BETWEEN 1 AND 6 AND foto='y' GROUP BY totalbedrooms ORDER BY totalbedrooms");
while ($row=mysql_fetch_assoc($res)) {
    $varname = "pgl_v_" . $row['beds'];
    $$varname = $row['cnt'];
}

echo $pgl_1 . ' ' . $pgl_v . ' ' . $pgl_v_1 . ' ' . $pgl_v_2 . ' ' . $pgl_v_3 . ' ' . $pgl_v_4 . ' ' . $pgl_v_5 . ' ' . $pgl_v_6;[/code]

Share this post


Link to post
Share on other sites
[code]
$res=mysql_query(" SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and rentaltype='holiday rental' and foto='y'");    
$row=mysql_fetch_assoc($res);
mysql_free_result($row);
$pgl=$row['cnt'];
[/code]

In the above example, I have changed just one query before continuing to modify all of my queries, but I am getting the following error message. The query executes despite displaying the error.

[code]
Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /homepages/xxxx
[/code]

If I remove the mysql_free_result line, the query still executes ok.
What is causing this error?
Do I need to use the mysql_free_result?

Share this post


Link to post
Share on other sites
[code]$res=mysql_query(" SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and rentaltype='holiday rental' and foto='y'");    
$row=mysql_fetch_assoc($res);
mysql_free_result($res);
$pgl=$row['cnt'];[/code]

mysql_free_result should be called on the result object, not the array created with mysql_fetch_assoc.. Use the above code instead.

Share this post


Link to post
Share on other sites
Yeah, my mistake. That's what I get for posting untested code :)

mysql_free_result() isn't necessary, just good practice. It frees up memory so PHP can reuse it.

Share this post


Link to post
Share on other sites
I have been busy changing a few things after having a bit of a brainwave.

Instead of selecting * in the queries, I decided that as I was only counting the number of rows, I could do a select on just the primary key so instead of SELECT *, I now have SELECT a4m.

Then last night I realised that I didn't actually have to query the database for all the counts. As villas is actually the total amount of 1,2,3,4,5,6,7 bedrooms all added up, I decided to add these together using php.
This has saved approx 10k on the page size and has removed some 70 queries to the database.

[code]
$pgl_v_1=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y'"));

$pgl_v_2=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='2' and foto='y'"));

$pgl_v_3=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='3' and foto='y'"));

$pgl_v_4=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='4' and foto='y'"));

$pgl_v_5=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='5' and foto='y'"));

$pgl_v_6=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='6' and foto='y'"));

$pgl_v_7=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms>=7 and foto='y'"));

$pgl_a_1=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='apartment' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y'"));

$pgl_a_2=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='apartment' and rentaltype='holiday rental' and totalbedrooms='2' and foto='y'"));

$pgl_a_3=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='apartment' and rentaltype='holiday rental' and totalbedrooms='3' and foto='y'"));

$pgl_a_4=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='apartment' and rentaltype='holiday rental' and totalbedrooms='4' and foto='y'"));

$pgl_f_1=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='farmhouse' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y'"));

$pgl_f_2=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='farmhouse' and rentaltype='holiday rental' and totalbedrooms='2' and foto='y'"));

$pgl_f_3=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='farmhouse' and rentaltype='holiday rental' and totalbedrooms='3' and foto='y'"));

$pgl_f_4=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='farmhouse' and rentaltype='holiday rental' and totalbedrooms='4' and foto='y'"));

$pgl_ah=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and rentaltype='holiday rental' and foto='y' and propertytype='apartment hotel'"));

$pgl_lc_1=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='log cabin' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y'"));

$pgl_lc_2=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='log cabin' and rentaltype='holiday rental' and totalbedrooms='2' and foto='y'"));

$pgl_lc_3=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='log cabin' and rentaltype='holiday rental' and totalbedrooms='3' and foto='y'"));

$pgl_lc_4=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='log cabin' and rentaltype='holiday rental' and totalbedrooms='4' and foto='y'"));

$pgl_c_1=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='cottage' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y'"));

$pgl_c_2=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='cottage' and rentaltype='holiday rental' and totalbedrooms='2' and foto='y'"));

$pgl_c_3=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='cottage' and rentaltype='holiday rental' and totalbedrooms='3' and foto='y'"));

$pgl_c_4=mysql_num_rows(mysql_query(" SELECT a4m FROM table WHERE country='Portugal' and propertytype='cottage' and rentaltype='holiday rental' and totalbedrooms='4' and foto='y'"));

//Calculate the total amount of villas in portugal
$pgl_v = $pgl_v_1 + $pgl_v_2 + $pgl_v_3 + $pgl_v_4 + $pgl_v_5 + $pgl_v_6 + $pgl_v_7;
//Calculate the total amount of apartments in portugal
$pgl_a = $pgl_a_1 + $pgl_a_2 + $pgl_a_3 + $pgl_a_4;
//Calculate the total amount of farmhouses in portugal
$pgl_f = $pgl_f_1 + $pgl_f_2 + $pgl_f_3 + $pgl_f_4;
//Calculate the total amount of log cabins in portugal
$pgl_lc = $pgl_lc_1 + $pgl_lc_2 + $pgl_lc_3 + $pgl_lc_4;
//Calculate the total amount of cottages in portugal
$pgl_c = $pgl_c_1 + $pgl_c_2 + $pgl_c_3 + $pgl_c_4;
//Calculate the total amount of all properties in portugal
$pgl = $pgl_v + $pgl_a + $pgl_f + $pgl_ah + $pgl_lc + $pgl_c;
[/code]

The block of code above is repeated 12 times to display properties in 12 different countries.

The menu that the results from above are displayed in is slow sometimes and quick at other times so I am wondering if there is something else in my website which is overloading the database.
The menu has to be loaded after the header due to the xhtml/css layout of my website. Because of this, if my menu does not load, then the header loads but the rest of my website does not display.

Share this post


Link to post
Share on other sites
[!--quoteo(post=352544:date=Mar 7 2006, 10:59 AM:name=algarve4me)--][div class=\'quotetop\']QUOTE(algarve4me @ Mar 7 2006, 10:59 AM) [snapback]352544[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have been busy changing a few things after having a bit of a brainwave.

Instead of selecting * in the queries, I decided that as I was only counting the number of rows, I could do a select on just the primary key so instead of SELECT *, I now have SELECT a4m.
[/quote]

A step in the right direction, but again, you're calling unnecessary functions and using up memory where you don't need to. If you have 10000 rows in the database and you select a4m from all those rows, you get all 10000 returned to the program. That's a lot of data that needs to be moved and stored in memory. That takes time.

Instead, get rid of that and use this :

[code]
list($pgl)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and rentaltype='holiday rental' and foto='y'"));
[/code]

The key here is that you're using COUNT(*) as opposed to selecting all the data. The above query returns a single number. One row. Probably only a few bytes worth of memory used. Low impact and very quick. MySQL has all sorts of optimizations to do stuff like this quickly. Instead of relying on PHP to count the number of rows, let SQL do it for you!!!

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
The menu that the results from above are displayed in is slow sometimes and quick at other times so I am wondering if there is something else in my website which is overloading the database.
The menu has to be loaded after the header due to the xhtml/css layout of my website. Because of this, if my menu does not load, then the header loads but the rest of my website does not display.
[/quote]

Your poor machine is getting exhausted using up all that memory.. Give the above suggestion a try and see if that speeds things up.

Share this post


Link to post
Share on other sites
Why post here if you're not even going to read the replies? USE SELECT COUNT(*) FOR PETE'S SAKE!

Share this post


Link to post
Share on other sites
[!--quoteo(post=352591:date=Mar 7 2006, 02:58 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 7 2006, 02:58 PM) [snapback]352591[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Why post here if you're not even going to read the replies? USE SELECT COUNT(*) FOR PETE'S SAKE!
[/quote]

Hrm.. who's pete? :)

Share this post


Link to post
Share on other sites
Try a single query to count and storing in an array

[code]$r=mysql_query("SELECT propertytype, totalbedrooms, COUNT(*)
                FROM table
                WHERE country='Portugal'
                      AND  foto='y'
                      AND rentaltype='holiday rental'
                GROUP BY propertytype, totalbedrooms");

$totalProps = 0;
$props = $counts = array();
while (list($type, $beds, $num) = mysql_fetch_row($r)) {
       $totalProps += $num;
       $props[$type][$beds] = $num;
       $counts[$type] += $num;
}

echo "Country : Portugal ($totalProps)<br>";
foreach ($props as $type => $data) {
         echo "$type ({$counts[$type]})<br>";
         foreach ($data as $beds => $num) {
                  echo "$beds bedrooms $type ($num)<br>";
         }
}[/code]

Share this post


Link to post
Share on other sites
Thank you for your help.

I believe that I have resolved the problem although not totally with the database queries.

I had previously tried changing the Select to COUNT(*) as cnt, but it hadn't had any noticeable effect.

What I have done is looked at the menu as a whole and I think that it had too many links in it(around 400) and was adding too much weight to the actual page size. I have now split the 12 countries down into separate country page classes. This way I am only loading the main menu for each page with around 35 links instead of 400.

I have read your posts and taken on board your suggestions.
It seems that to use COUNT is quicker than to use mysql_num_rows (although there is still some debate out there on some of the forums), I have now amended each of my queries to read as follows:-

[code]
list($pgl_v_1)=mysql_fetch_array(mysql_query(" SELECT COUNT(*) as cnt FROM table WHERE country='Portugal' and propertytype='villa' and rentaltype='holiday rental' and totalbedrooms='1' and foto='y'"));
[/code]

My pages always seem to load a lot quicker in the evening so I am wondering if there is a traffic problem on my hosted server or if I have another area of my site which is putting too much load on the database. At this precise moment, my pages load fairly quick but I will see how they are tommorrow.

Share this post


Link to post
Share on other sites
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]It seems that to use COUNT is quicker than to use mysql_num_rows (although there is still some debate out there on some of the forums)[/quote]

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.

Share this post


Link to post
Share on other sites
[!--quoteo(post=353148:date=Mar 8 2006, 11:59 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 8 2006, 11:59 PM) [snapback]353148[/snapback][/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?

Share this post


Link to post
Share on other sites
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. :)

Share this post


Link to post
Share on other sites
[!--quoteo(post=353244:date=Mar 9 2006, 09:05 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 9 2006, 09:05 AM) [snapback]353244[/snapback][/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.. :)

Share this post


Link to post
Share on other sites
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:-
[code]
$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'"));
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.