Jump to content


Photo

Too many database queries load my page too slow


  • Please log in to reply
23 replies to this topic

#1 algarve4me

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

Posted 03 March 2006 - 02:42 PM

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

//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);

Any help would be greatly appreciated.

#2 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 03 March 2006 - 02:49 PM

Do you have the search columns indexed?
--
[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]

#3 algarve4me

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

Posted 03 March 2006 - 03:16 PM

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.

#4 Lt Llama

Lt Llama
  • Validating
  • PipPip
  • Member
  • 11 posts

Posted 03 March 2006 - 04:21 PM

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

#5 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 03 March 2006 - 10:22 PM

"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:

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


#6 algarve4me

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

Posted 04 March 2006 - 10:43 PM

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.

#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 05 March 2006 - 02:24 AM

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.

#8 jajtiii

jajtiii
  • Members
  • PipPipPip
  • Advanced Member
  • 43 posts

Posted 05 March 2006 - 03:17 AM

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.

#9 algarve4me

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

Posted 05 March 2006 - 06:21 PM

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.

$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'"));


#10 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 05 March 2006 - 10:44 PM

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.

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


#11 algarve4me

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

Posted 06 March 2006 - 01:54 PM

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

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.

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

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?

#12 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 06 March 2006 - 02:46 PM

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

mysql_free_result should be called on the result object, not the array created with mysql_fetch_assoc.. Use the above code instead.
--
[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]

#13 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 06 March 2006 - 02:48 PM

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.

#14 algarve4me

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

Posted 07 March 2006 - 03:59 PM

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.

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

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.

#15 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 07 March 2006 - 07:05 PM

[!--quoteo(post=352544:date=Mar 7 2006, 10:59 AM:name=algarve4me)--][div class=\'quotetop\']QUOTE(algarve4me @ Mar 7 2006, 10:59 AM) View Post[/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 :

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

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

#16 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 07 March 2006 - 07:58 PM

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

#17 XenoPhage

XenoPhage
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 07 March 2006 - 08:06 PM

[!--quoteo(post=352591:date=Mar 7 2006, 02:58 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 7 2006, 02:58 PM) View Post[/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? :)
--
[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]

#18 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 07 March 2006 - 08:23 PM

Try a single query to count and storing in an array

$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>";
         }
}

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#19 algarve4me

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

Posted 08 March 2006 - 11:45 PM

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

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'"));

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.



#20 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 09 March 2006 - 04:59 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users