Slow SQL Query 1

[ljwilson]

This query takes over a minute to run. I realize that some of this is PHP code, but was hoping that someone could tell me how to speed this up (if possible).

$sql=	"SELECT p.paymentmethod AS METHOD, SUM(amount * quantity) AS " .
						"TOTAL, COUNT(DISTINCT(transactionid)) AS COUNT FROM tblTransactions t, " . 
						"tblPayments p WHERE t.reportid= " . $reportid . " AND t.transactionnum " .
						"= p.transactionnum AND t.transdepartment = 'ROA' AND t.storeid = '" . 
						$storeid . "' AND t.registernum = '" . $registernum . "' GROUP BY " .
						"p.paymentmethod ";
				
				$rs = $db->query($sql,false); 
				while ($row = $db->fetch_array($rs))				
				{
					extract($row);
					$total_roa[$METHOD] = $TOTAL;
					$count_roa[$METHOD] = $COUNT;
					$line .= str_pad($METHOD . " ROA", 20," ", STR_PAD_RIGHT) . str_pad($COUNT,7," ",STR_PAD_LEFT) . "\n";
					$line .= str_pad("$".$TOTAL,27," ", STR_PAD_LEFT) . "\n\n";	
				}

Thanks!

 

[towerbase]

Unexpectedly slow queries are usually caused by not indexing the tables (properly).

Ensure you have appropriate indexes for the columns specified in your WHERE clause.

Andrew
Hampshire, UK

 

[ljwilson]

I added an index for transactionnum (tnum_index) on both tblTransactions and tblPayments. Here is the before and after results of an explain query:

EXPLAIN SELECT p.paymentmethod AS METHOD, SUM(amount * quantity) AS TOTAL, COUNT(DISTINCT(transactionid)) AS COUNT FROM tblTransactions t, tblPayments p WHERE t.reportid=861 AND t.transactionnum = p.transactionnum AND t.transdepartment = 'ROA' AND t.registernum = 1 GROUP BY p.paymentmethod;

Before Results:
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 95160 | Using temporary; Using filesort |
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 173578 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+

Query used to make the indexes

CREATE INDEX tnum_index ON tblTransactions(transactionnum);

CREATE INDEX tnum_index ON tblPayments(transactionnum);

After Results:
+----+-------------+-------+------+---------------+------------+---------+----------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+----------------------+--------+----------------------------------------------+
| 1 | SIMPLE | t | ALL | tnum_index | NULL | NULL | NULL | 173578 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | tnum_index | tnum_index | 5 | pos.t.transactionnum | 1 | Using where |
+----+-------------+-------+------+---------------+------------+---------+----------------------+--------+----------------------------------------------+

Time difference before and after:
Before - 1 min 6 seconds
After - 1 second

Can you look at what I did and let me know if you see a problem with how I created the indexes. The report came out the same (just 65 seconds faster!), but I want to make sure I didn't hurt anything.

Thanks so much!