为什么我的mysql查询需要太长时间才能产生结果

Question

Hi i wrote the following code to retrieve data from a database, but its too slow on its execution, can anybody have a look on it and give a few optimizing tips for better performance.

what really happens is im creating a new table by binding existing current data from few other tables.

$table_count = mysql_query("SELECT TABLE_NAME FROM information_schema.tables 
WHERE table_schema = 'datamatrix' 
AND table_name 
LIKE 'tracking_%' ");

while($row = mysql_fetch_array($table_count)){

$trackingtable = $row["TABLE_NAME"];

$update = mysql_query("SELECT id as unp FROM $trackingtable");
$col = mysql_fetch_assoc($update);
$col_id = ($col["unp"]);

$var1 = mysql_query("SELECT useragent as ua 
FROM trackpanel 
WHERE id = $col_id");
$col_ua = ($var1["ua"]);
$browser = get_browser($col_ua, true);


$new_timestamp = mysql_query("SELECT TIMESTAMP FROM $trackingtable 
ORDER BY TIMESTAMP 
DESC LIMIT 1");
        $col_new = mysql_fetch_assoc($new_timestamp);

$new_timestamp1 = mysql_query("SELECT TIMESTAMP FROM $trackingtable 
ORDER BY TIMESTAMP 
ASC LIMIT 1");
$col_old = mysql_fetch_assoc($new_timestamp1);

mysql_query("
INSERT INTO report (`trackingID`,`trackname`,`accountname`,`accountID`, `status`, `clickcount`, `earliest_click`,`recent_click`,`platform`,`device`,`browser`,`browser_version`)
SELECT b.trackingID, `trackname`, accountname, c.accountID, status, total_clicks,'".$col_new['TIMESTAMP']."','".$col_old['TIMESTAMP']."','".$browser['platform']."','".$browser['device_name']."','".$browser['browser']."','".$browser['version']."'
FROM $trackingtable a, datamat b, trackaccounts c
WHERE a.timestamp > DATE_ADD(NOW(), INTERVAL -2 YEAR) AND a.trackingID = b.trackingID AND b.accountID = c.accountID") 
or die(mysql_error());
} 

?>

Answer 1

Without knowing how your database is set up and indexed, and what table storage engines you are using, I can only guess as to why its slow.

1. Your first query could return multiple rows consisting of table names
2. You run 4 simple select statements.
3. You run an insert with a select statement. The select query here also joins 3 tables together

The second and third parts of the list are run for each row which gets looped from the first query. If your tables are InnoDB, the selects will be slower whereas the insert is faster. Opposite is true for MyISAM.

Each query has its own latency because it queries the database separately. You would be better off combining the 1st, 3rd and 4th queries in the while loop as they query the same table.

The insert and select query I guess will most probably be the slowest since it joins 3 tables. That select query can definitely be optimised.