Page 1 of 1

Speeding up MySQL query??

Posted: Mon Jun 16, 2003 7:12 pm
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

Posted: Mon Jun 16, 2003 7:55 pm
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.

Posted: Tue Jun 17, 2003 10:02 am
by tsg
What if the results of the first query was put into an array, then run the second query from the array?

Posted: Tue Jun 17, 2003 10:14 am
by cactus
What columns have you created and index on, in mySQL ?

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

Regards,

Posted: Tue Jun 17, 2003 10:26 am
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

Posted: Tue Jun 17, 2003 1:58 pm
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,