| Re: Speeding up of php page [message #178928 is a reply to message #178925] |
Sun, 26 August 2012 13:24   |
Norman Peelman
Messages: 126
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 08/26/2012 06:15 AM, houghi wrote:
> 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
>
Three things here...
#1 - Why are you calling the database twice?
#2 - If you already have the movie id (tconst) then drop the LIKE and
just use '='. Using LIKE causes a full table scan, in your example a
full table scan is performed 338 times (how big is your db?)
Set up indexes on qconst and tconst and try:
$sql = "SELECT char, nconst, stage, quote FROM quotes WHERE tconst =
'$tconst' GROUP BY qconst";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo '<p>';
if (isset($row['char']))
{
if (isset($row['nconst']))
{
echo "<b><a href='http://www.imdb.com/name/$row[nconst]/'
TARGET='imdb'>$row[char]</a> </b>";
} else {
echo "<b>$row[char]</b> ";
}
}
if ($row['stage'] !== '')
{
echo "<i> [ $row[stage] ] </i>";
}
if (isset($row['quote']))
{
echo $row['quote'];
}
echo '<br>';
}
}
echo "<hr align='left' size='2' width='60%' color='#0F0F0F' noshade></p>\n";
#3
I see 4 tables here:
movies
------
quotes
------
person
------
character
---------
Properly linked, it should only require one call to the database to
retrieve your data. I didn't take the time to hunt for/download your
tool so I don't know what options you have for database setup.
p.s. - Pardon my changing of your quoting style...
--
Norman
Registered Linux user #461062
-Have you been to www.php.net yet?-
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]