| Speeding up of php page [message #178925] |
Sun, 26 August 2012 10:15  |
houghi
Messages: 45
Registered: September 2011
Karma:
|
Member |
|
|
I have a page that takes 7.4 seconds to load. I believe this is due to
the bad way it asks for the MySQL data.
I have moved the MySQL database from HD to SSD and even to memory, but
there was no significant increase in speed. This makes me believe it is
the way I (badly) wrote the queries and/or the building of the page.
That database contains imdb data and in this case quotes.¹
The structure of the database is:
id, tconst, qconst, count, char, nconst, stage, quote
id is autoincrment
tconst is the movie ID
qconst is the quote ID
count is the quote counter. Starts at 1 with each new quote
char is the character
nconst is the persons ID
stage is the setting. This is not always filled in
quote is the actual quote. This is not always filled in (e.g. if there
is only a 'stage' that says "last lines"
What I want to show is all the quotes of a certain movie of which I
already have the id and quotes are already in my database.
For The Matrix, which takes 7,4 seconds, I have 337 lines that contain the
correct movie ID.
7,4 seconds is extremely slow, I would say. At least for that limited
amount of data.
Here is the code:
<?php
#Show the quotes
//$tconst='tt0133093';
echo "<h1>Quotes</h1>";
$sql = "SELECT qconst FROM quotes WHERE tconst LIKE \"$tconst\" GROUP BY qconst";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo '<p>';
$qconst = $row['qconst'];
$sql2 = "SELECT * FROM quotes WHERE qconst LIKE \"$qconst\"";
$result2 = mysql_query($sql2) or die(mysql_error());
while($row2 = mysql_fetch_array($result2))
{
if (isset($row2['char']))
{
if (isset($row2['nconst']))
{
echo '<b><a href="http://www.imdb.com/name/',$row2['nconst'],'/" TARGET="imdb">',$row2['char'],'</a> </b>';
} else {
echo '<b>',$row2['char'],'</b> ';
}
}
if ($row2['stage'] !== '')
{
echo '<i> [ ',$row2['stage'],' ] </i>';
}
if (isset($row2['quote']))
{
echo $row2['quote'];
}
echo '<br>';
}
echo '<hr align="left" size="2" width="60%" color="#0F0F0F" noshade></p>',"\n";
}
mysql_close($conn);
?>
So what can I do to seriously speed up. And more importandly, where did
I mess up in my thought process that it takes 7.4 seconds to show 337
lines from a local database on a local webserver. An increaso of factor
10 should be achievable.
Other pages with much less data load in 0.3 seconds and that includes
loading of several images. I can imagine if I am able to increase the
speed of the quotes, I will be able to increase the speed of everything
else.
¹The database is build up with e.g.²:
#!/bin/bash
REST='appid=v1&device=iphone1_1&sig=6b48237c2e2544305e6d303c4f67654 d'
curl "http://app.imdb.com/title/quotes?tconst=tt0111161&$REST" \
| python -mjson.tool|less
http://code.google.com/p/imdb-php/ will give more information
concerning the retrieval of ImDB data via the API.
houghi
--
This is written under the inluence of the following:
> Artist : Philip Glass
> Song : Warszawa
> Album : Symphony No. 1
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]