Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
| Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 13th, 2004, 04:18 AM
Registered User
 
Join Date: Apr 2004
Location: Novi Sad, Serbia, Yugoslavia.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Speed up executing SQL in PHP

While simple SELECT from database and print data over the screen I have visible delay in print if in table are more than 2000 records. Is there some general way to speed up executing SQL queries in PHP?
Thanks!!!
 
Old April 13th, 2004, 11:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

THere are all sorts of ways you can speed up SELECTs, yes, but you don't even tell us what database daemon you are using ;0.

Why don't you give us some idea of the actual query you are running and a rough idea of what the tables look like. MySQL's query optimiser is very good at running queries efficiently (and you can use the EXPLAIN keyword to ask to get some bare data on what it is doing), but it is still possible to force inefficiency upon it by, say, running the main search clause against your resultset before running a subclause that could radically reduce the number of records searched.

There's things like MySQL's Sprocs, as well, if you have access to them, but for all I know at the moment, you could be using Access 97 ;).

In any case, 2000 records doesn't sound a lot, if it really is a simple SELECT.

BTW, it goes without saying that you never do "SELECT * ..."
 
Old April 14th, 2004, 02:51 AM
Registered User
 
Join Date: Apr 2004
Location: Novi Sad, Serbia, Yugoslavia.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Daniel,
Cool to hear from you. Seems as I missed to describe my trobule properly.
All right, I'd try again...please let me know if I missed something...


1.0 I use MySQL 4.0.16 (no Access)

2.0 There is one simple table which looks like:
CREATE TABLE Test_Table (
  z_PRIMARY_KEY bigint(17) NOT NULL auto_increment,
  xx_TimeStamp timestamp(14) NOT NULL,
  First_Field varchar(64) default NULL,
  Second_Field varchar(64) default NULL
) TYPE=MyISAM;

3.0 So you said this query has to be faster :
- SELECT First_Field, Second_Field FROM Test_Table
Than this one :
- SELECT * FROM Test_Table

You're right but on half because it doesnt help enough when your database has to PRINT 50.000 records.

4.0 When I check out my SQL query in shell or phpMyAdmin it runns in speedlight.
    Trouble comes out when I have to PRINT First_Field and/or Second_Field.
    First obvious delay(but very,very short) is on LIMIT 0, 2000 records and later makes increase (as you make LIMIT bigger.)

5.0 Was digging through the internet to find some solution for this problem but seems as I look for Holly Grale.
 
Old April 14th, 2004, 11:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, if you are printing out fifty thousand records, you are certainly going to experience delays. The choice of varchar(64) for both the fields being sought isn't helping, either, I'm afraid, since varchars are always going to be harder to search for than fixed length fields (the end of each field has to be searched for, whereas a fixed length field of 64 characters is always 64 characters long). Could you afford the storage hit by adopting fixed length fields?

I see you are not, as yet, specifying a WHERE clause in your SELECT, but when you do, you will seriously benefit from using Index values:
http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html
Hash indexes, in particular, would sidestep the variable length problems...
http://dev.mysql.com/doc/mysql/en/HEAP.html

(As with almost every page of the MySQL online manual, at the moment, both these explanations are, rather frustratingly, full of stuff about how great things will be when version 4.1 comes out, I'm afraid :).

If your rate-limiting-step is the actual _output_ of the fields, you may wish to look at more efficient methods of printing out fifty thousand lines of text. This may call for some experimentation. HEREDOCs, or some similar method of output which doesn't rely on context-switching, may prove more effective than fifty thousand lines-worth of:

foreach($row=mysql_fetch_assoc($result){
   $rowcolor = (++count%2)? "light_row" : "dark_row";
   echo "<tr class=\"rowcolor\">"; ?>
     <td width="presentational kludge" stuff="other presentational hacks">
   <?php
   echo $row['First_Field,'];
   ?>
   </td>
... etc. (I'm sure you get the picture).

It'll also be much more readable.

As a postscript, remember that if things really do get desperate, native functions, and numerically index arrays, are always faster than more modern approaches such as DB::PEAR - or even fetch_assoc.

We tend to forget in this day of dual Athlon desktops and the 2.6 kernel: objects are Slooow ;).
 
Old April 15th, 2004, 08:14 AM
Registered User
 
Join Date: Apr 2004
Location: Novi Sad, Serbia, Yugoslavia.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

1.0 I agree about char() and varchar().
2.0 Also agree about MySQL manual :(
3.0 I never used HEREDOC method...I've searched through the internet, and all what I could figured out about it was, that we use HEREDOC in making double quotes ""...but really, I cant understand how it could help in printing. But one is sure, the thing which makes slow down is loop, O.O. supported :
for ($i = 0; $i < $db->rows; $i++) {
      $db->fetch($i);
  print $db->data[First_Field];
};
All of them(objects) are supportet by functions made of native functions, if you mean native function is something like "@mysql_select_db"?
Anyway, I have to explore much more "methods of output" and mend the hole in my knowledge.
Thanks Daniel !!!
 
Old April 15th, 2004, 09:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

A HEREDOC escapes the need to escape quotes :)! It's actually an import from Perl.

Compare:

<tr>
    <td span="2">Weather Statistics for <?php echo $date1 ?> to <$php echo date2; ?>:</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($result)){
$date = stripslashes($row['date']);
$rainfall = stripslashes($row['rainfall']);
$temperature = stripslashes($row['temperature']);
$temperature = htmlspecialchars($temperature);
$humidity = stripslashes($row['humidity']);
$pressure = stripslashes($row['pressure'];
?>
<tr class="<?php echo $rowclass; ?>">
     <td rowspan="4"><?php echo $date; ?></td>
     <td>Rainfall</td>
     <td><?php echo $rainfall; ?> millimeters</td>
</tr>
<tr class="<?php echo $rowclass; ?>">
     <td>Temperature (avg.)</td>
     <td><?php echo $temperature; ?> &176; C</td>
</tr>
<tr class="<?php echo $rowclass; ?>">
     <td>Humidty</td>
     <td><?php echo $humidity, ?>%</td>
</tr>
<tr class="<?php echo $rowclass; ?>">
     <td>Barometric Pressure</td>
     <td><?php echo $pressure; ?>mmHg</td>
</tr>
<?php
}
?>

...etc. With:

<tr>
    <td span="2">Weather Statistics for <?php echo $date1 ?> to <$php echo date2; ?>:</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($result)){
$date = stripslashes($row['date']);
$rainfall = stripslashes($row['rainfall']);
$temperature = stripslashes($row['temperature']);
$temperature = htmlspecialchars($temperature);
$humidity = stripslashes($row['humidity']);
$pressure = stripslashes($row['pressure'];

echo <<<EOT
<tr class="$rowclass">
     <td rowspan="4">$date</td>
     <td>Rainfall</td>
     <td>$rainfall millimeters</td>
</tr>
<tr class="$rowclass">
     <td>Temperature (avg.)</td>
     <td>$temperature &176;C</td>
</tr>
<tr class="$rowclass">
     <td>Humidty</td>
     <td>$humidity%</td>
</tr>
<tr class="$rowclass">
     <td>Barometric Pressure</td>
     <td>$pressure mmHg</td>
</tr>

EOT;
<?php
}
?>

The format is to begin the HEREDOC with <<<ASTRING, where ASTRING is simply the identifier for the end of the HEREDOC. Like others, I tend to use EOT, as being short for 'End Of Text', but you can use what you like, as long as you put your end-identifier at the start of a newline, with no whitespace or other characters infront of it and a semi-colon at the end. HEREDOCs get very useful when you start using mail, where the format of the header of the email message is far more than a choice of aesthetics! I like them, and use them generally, since large amounts of switching, or escaping interrupt your understanding of what is being output, even when they DON'T slow things down!

The important point to note, is that whether using:

echo "<tr class=\"$rowclass\">"

...or using HEREDOCs, you are effectively pushing all your ouput throught the PHP parser, rather than piping bits direct through the webserver and parsing other bits. Piping is considerably faster, of course, but the context-switch between simple piping and invoking the parser imposes a slight time lag of its own. The key is to identify whether, when, say, outputting the text:

<tr class="$rowclass">
     <td>Humidty</td>
     <td>$humidity%</td>
</tr>

...performing context-switch at the end of outputting '$rowclass', and then piping the next 34 characters (36 on Windows) straight through the server, before invoking the parser again to output whatever '$humidity' contains, actually takes longer than simply pushing all the text through the parser and forgoing the two context-switches. It's a debatable point, and depends on how often context is switched, and how much text could be simply piped between switches. This is a much less critical point in PHP, than in other technologies such as ASP, which invokes an entire object to do the job of the echo statement.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Executing an SQL query and using it's result Andrew.Berry ASP.NET 2.0 Professional 5 April 14th, 2008 08:25 AM
speed up slow Perl code with SQL statements crmpicco Perl 3 May 11th, 2007 05:21 AM
Trouble executing SQL statement turbo_rabbit ASP.NET 1.0 and 1.1 Basics 13 July 6th, 2005 01:31 PM
executing sql command redface Crystal Reports 0 April 6th, 2005 05:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.