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());
}
?>
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.
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.
The only reason why your queries may be slow is because of a lack on index on your trackingtable, even if you didn't specify which query was slow.
Check for a index presence on