Speeding up MySQL query??

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Speeding up MySQL query??

Post by tsg »

I have a stats / traffic program I have been working on off and on for a little while. I am trying to speed up the results pages because it is talking a long time to produce the query.

Bassically I have 2 tables. 1) visitor information and 2) page view information. Note this has been scaled down to help make since

first query:

Code: Select all

<?php
$result = @mysql_query("SELECT stats_id, etc, etc FROM stats ORDER BY st_id DESC LIMIT 20" ); 
?>
for each result I query again:

Code: Select all

<?php
$result2 = @mysql_query("SELECT pv_id,  COUNT(*) AS tpv FROM statspv WHERE pv_ref_id='$stats_id' GROUP BY pv_ref_id" );

$row = mysql_fetch_array($result2);
$tpv = $row["tpv"]; 
?>
I have over 100,000 rows in the statspv table and it taked a long time to load this wey. Is there a better / faster way to do this?

Thanks for any input.
Tim
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

You could try a single JOIN query - but I think the size of the db means nothing is going to be lightning fast.
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Post by tsg »

What if the results of the first query was put into an array, then run the second query from the array?
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

What columns have you created and index on, in mySQL ?

Reference: http://www.mysql.com/doc/en/MySQL_indexes.html

Regards,
tsg
Forum Contributor
Posts: 142
Joined: Sun Jan 12, 2003 9:22 pm
Location: SE, Alabama
Contact:

Post by tsg »

Well how about that!

I went in to phpmyadmin and indexed 2 colums (wasn't done before). Now it querys very fast!

Wow, what a difference. I am going to tweek some stuff but that is obviously the answer.

Thanks. I will read up more in the index, but anything that should be known about that? like dos and don'ts?

Thanks again,
Tim
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

No problem, also you could think about a schedule of optimisations if you have deleted a lot of rows:
mySQL Manual wrote:OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns).
You can also do this from phpMyAdmin, use of it does (obviously) depend on your application/system/service/load but any improvement in performance is welcome.

Regards,
Post Reply