MySQL + PHP = SLOW, but...

WolfEyes

I got a simple php page which is awfully slow. I figured out by using timestamps that MySQL is NOT the problem, the queries are fast. It seems that the PHP-code which creates a table is the problem. When I access the page with the PHP it takes around 5-7 seconds to display 27 rows and the harddisk writes like mad (php.ini : output-buffering=4096). All the script does is create HTML for each cell (using different colors for odd and even rows):

<?php
$i = 0;
echo "createtable_start:".time();
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "line:". $i . " ".time();
$i++;

echo "\t<tr class=\"";
if ($i%2 == 0) {echo "GERADE";}
else {echo "UNGERADE";}
echo "\">\n";

echo "\t\t<td class=\"datum\">" . substr($line[Zeitstempel], 8, 2).".".substr($line[Zeitstempel], 5, 2). ".</td>\n";
echo "\t\t<td class=\"zeit\">" .substr($line[Zeitstempel], 11, 2).":".substr($line[Zeitstempel], 14,2). "</td>\n";
echo "\t\t<td class=\"kanal\">" .$line[Kanal]. "</td>\n";
echo "\t\t<td class=\"betriebsart\">" .$line[Betriebsart]. "</td>\n";
echo "\t\t<td class=\"an\">" .$line[An]. "</td>\n";
echo "\t\t<td class=\"von\">" .$line[Von]. "</td>\n";
echo "\t\t<td class=\"ereignis\">" .htmlspecialchars($line[Ereignis]). "</td>\n";
echo "\t\t<td class=\"vermerk\">" .htmlspecialchars($line[Vermerk]). "</td>\n";

}
echo "createtable_stop:".time();

/ Freigeben des Resultsets /
mysql_free_result($result);

/ schliessen der Verbinung /
mysql_close($link);
?>

ANY IDEAS?? 😕 😕 😕

Here is my config:
Windows 2000 SP4 (AMD 2400+, 512M😎
Apache 2.0.48 (as service)
PHP 4.3.4
MySQL 4.0.17

WolfEyes

createtable_start:1076508685
line:0 1076508685
line:1 1076508685
line:2 1076508685
line:3 1076508685
line:4 1076508685
line:5 1076508685
line:6 1076508686
line:7 1076508686
line:8 1076508686
line:9 1076508686
line:10 1076508686
line:11 1076508686
line:12 1076508687
line:13 1076508687
line:14 1076508687
line:15 1076508687
line:16 1076508687
line:17 1076508688
line:18 1076508688
line:19 1076508688
line:20 1076508688
line:21 1076508689
line:22 1076508689
line:23 1076508689
line:24 1076508689
line:25 1076508690
line:26 1076508690
createtable_stop:1076508690

Creating a single row takes about 200ms :mad:

Sxooter

Have you tried creating a static data structure and displaying the table with that to compare the performance? I'm guessing that the mysql_fetch_array() is the slowest part of all this. But I'm just guessing.

Note, you can also use microtime to wrap around single functions to get an idea how fast they are running.

AstroTeg

What's you're query look like?

A poorly constructed query or a poorly designed database can be a result of your performance issues. But there's not enough info to determine for sure.

If you copy and paste the query in phpmyadmin, how long does it take to bring the rows back? If its the same amount of time, then you have a query/database design issue. If phpmyadmin comes back pretty quick, then you probably have a PHP issue.

WolfEyes

@: Static data structure?? What do you mean? Hardcoded HTML? I tried the microtime() function with the same results, although now with greater precision.

The instruction "$line = mysql_fetch_array($result, MYSQL_ASSOC)" takes 0.0001 seconds.

Here the results for the rest:
TR_start took 0 sec. and 1.7000000000003E-005 ms.
TD_DATE took 0 sec. and 0.016705 ms.
TD_TIME took 0 sec. and 0.016583 ms.
TD_KANAL took 0 sec. and 0.016496 ms.
TD_BETRIEBSART took 0 sec. and 0.016571 ms.
TD_AN took 0 sec. and 0.016566 ms.
TD_VON took 0 sec. and 0.016498 ms.
TD_EREIGN took 0 sec. and 0.016567 ms.
TD_VERMERK took 0 sec. and 0.016562 ms.
TR took 0 sec. and 1.2999999999985E-005 ms.
TD_DATE took 0 sec. and 0.016528 ms.
TD_TIME took 0 sec. and 0.041425 ms.
TD_KANAL took 0 sec. and 0.016521 ms.
TD_BETRIEBSART took 0 sec. and 0.016561 ms.
TD_AN took 0 sec. and 0.016565 ms.
TD_VON took 0 sec. and 0.016567 ms.
TD_EREIGN took 0 sec. and 0.016575 ms.
TD_VERMERK took 0 sec. and 0.016551 ms.
TR took 0 sec. and 1.2000000000012E-005 ms.
..
SNIP
..
took 3 sec. and 0.712862 ms.

@: As I already mentioned the query IS NOT the issue. It completes in a few ms both in PHP, mysql.exe and MySQL Control Center. It's a PHP issue.

$query = "SELECT *, DATE_FORMAT(Zeitstempel, '%H:%i:%s') AS Zeit, DATE_FORMAT(Zeitstempel, '%d.%m.') AS Datum
FROM betriebsbuch_zeile, einsatz
WHERE einsatz.ID = '" . $EinsatzID ."'
ORDER BY Zeitstempel DESC;";

The table (how I get it back from MySQLCC):

CREATE TABLE betriebsbuch_zeile (
ID int(10) unsigned NOT NULL auto_increment,
EinsatzID int(10) unsigned NOT NULL default '0',
Zeitstempel datetime NOT NULL default '0000-00-00 00:00:00',
Kanal smallint(6) NOT NULL default '0',
Betriebsart set('GU','WU','GO','WO') NOT NULL default '',
An varchar(40) NOT NULL default '',
Von varchar(40) NOT NULL default '',
Ereignis varchar(200) NOT NULL default '',
Vermerk varchar(100) default NULL,
PRIMARY KEY (ID),
KEY EinsatzID (EinsatzID),
CONSTRAINT 0_133 FOREIGN KEY (EinsatzID) REFERENCES einsatz (ID)
) TYPE=InnoDB;

WolfEyes

IMHO it must be a cacheing issue, since the hard disk is being accessed for the whole request time..