| Speeding up of php page [message #178925] |
Sun, 26 August 2012 10:15  |
houghi
Messages: 45
Registered: September 2011
Karma: 0
|
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
|
|
|
|
| Re: Speeding up of php page [message #178926 is a reply to message #178925] |
Sun, 26 August 2012 10:37  |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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.
> $sql = "SELECT qconst FROM quotes WHERE tconst LIKE \"$tconst\" GROUP BY qconst";
Add an index on tconst
should give you about 100:1 speed up
--
Ineptocracy
(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.
|
|
|
|
| Re: Speeding up of php page [message #178927 is a reply to message #178926] |
Sun, 26 August 2012 11:43 |
houghi
Messages: 45
Registered: September 2011
Karma: 0
|
Member |
|
|
The Natural Philosopher wrote:
> 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.
>
>> $sql = "SELECT qconst FROM quotes WHERE tconst LIKE \"$tconst\" GROUP BY qconst";
>
> Add an index on tconst
>
> should give you about 100:1 speed up
Thanks for the pointer. The actual one was on qconst:
$sql2 = "SELECT * FROM quotes WHERE qconst LIKE \"$qconst\"";
Now speed is 0.04 seconds, so 200:1. :-D
Again thanks. Now I know what I need to read up on.
houghi
--
You can have peace. Or you can have freedom.
Don't ever count on having both at once.
-- Robert A. Heinlein
|
|
|
|
| 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: 0
|
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?-
|
|
|
|
| Re: Speeding up of php page [message #178934 is a reply to message #178928] |
Sun, 26 August 2012 17:34 |
houghi
Messages: 45
Registered: September 2011
Karma: 0
|
Member |
|
|
Norman Peelman wrote:
> #1 - Why are you calling the database twice?
Because I have no idea on how to do it differently. :-(
> #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?)
Thanks. phpMyAdmin tells me about quotes:
~69,655 total, Query took 0.0003 sec
> Set up indexes on qconst and tconst and try:
>
> $sql = "SELECT char, nconst, stage, quote FROM quotes WHERE tconst =
> '$tconst' GROUP BY qconst";
<snip>
Thanks. Will look into it.
> #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.
Where could I get information about this? What would be the search I
need to enter into google? The problem is that I learned by copy and
paste, so I have often no idea what the names are of what I am looking
for.
houghi
--
You can have peace. Or you can have freedom.
Don't ever count on having both at once.
-- Robert A. Heinlein
|
|
|
|
| Re: Speeding up of php page [message #178943 is a reply to message #178934] |
Sun, 26 August 2012 22:43 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 08/26/2012 01:34 PM, houghi wrote:
> Norman Peelman wrote:
>> #1 - Why are you calling the database twice?
>
> Because I have no idea on how to do it differently. :-(
>
Ok, understandable that you are learning... :) Basically with your
code you are asking the database for all the quote id(s) where the movie
id is a match to your movie, and then saving it to an array ($result).
Might as well grab all the data you're after while you're there the
first time, which is what my code should do.
>> #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?)
>
> Thanks. phpMyAdmin tells me about quotes:
> ~69,655 total, Query took 0.0003 sec
>
Is this after you created indexes on tconst and qconst? Without those
indexes your times will get significantly worst as you db gets bigger.
>> Set up indexes on qconst and tconst and try:
>>
>> $sql = "SELECT char, nconst, stage, quote FROM quotes WHERE tconst =
>> '$tconst' GROUP BY qconst";
> <snip>
>
> Thanks. Will look into it.
>
>
>> #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.
>
It only requires one call now, really.
> Where could I get information about this? What would be the search I
> need to enter into google? The problem is that I learned by copy and
> paste, so I have often no idea what the names are of what I am looking
> for.
>
>
> houghi
>
Search for 'database normalization'.
Look here: http://dev.mysql.com/doc/
And as an example:
http://dev.mysql.com/doc/refman/5.5/en/tutorial.html
--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-
|
|
|
|
| Re: Speeding up of php page [message #178974 is a reply to message #178943] |
Tue, 28 August 2012 16:33 |
houghi
Messages: 45
Registered: September 2011
Karma: 0
|
Member |
|
|
Norman Peelman wrote:
> Ok, understandable that you are learning... :) Basically with your
> code you are asking the database for all the quote id(s) where the movie
> id is a match to your movie, and then saving it to an array ($result).
> Might as well grab all the data you're after while you're there the
> first time, which is what my code should do.
Clear. I will see what I can do to get the same results. Learning can be
fun. :-D
>> Thanks. phpMyAdmin tells me about quotes:
>> ~69,655 total, Query took 0.0003 sec
>>
>
> Is this after you created indexes on tconst and qconst? Without those
> indexes your times will get significantly worst as you db gets bigger.
After. See a reply to an other poster. Increase was (as expected) 100
times measured on the php page itself.
> Search for 'database normalization'.
Thanks.
houghi
--
This is written under the inluence of the following:
> Artist : Mercedes Sosa
> Song : Cuando ya me Empiece a Quedar Solo
> Album : Mercedes Sosa en Argentina
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]