RE: FN-FORUM: Sql query help

date posted 13th January 2004 15:21

>Although it won't this list all suppliers will it?

Bugger you're right. Should be easy enough to sort though.

>Based on execution times with your data or from documentation somewhere

based on execution times.

Here's the output of explain for the sum()query:

+-------+--------+---------------+---------+---------+---------------+------
+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-------+--------+---------------+---------+---------+---------------+------
+---------------------------------+
| i | ALL | Supplier_ID | NULL | NULL | NULL | 2894
| Using temporary; Using filesort |
| s | eq_ref | PRIMARY | PRIMARY | 2 | i.Supplier_ID | 1
| Using where |
+-------+--------+---------------+---------+---------+---------------+------
+---------------------------------+
0.02 sec


and here it is for the joined query:
+-------+------+---------------+------+---------+------+------+-------------
--------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+------+-------------
--------------------+
| s | ALL | NULL | NULL | NULL | NULL | 13 | Using
temporary; Using filesort |
| p | ALL | Supplier_ID | NULL | NULL | NULL | 2894 |
|
+-------+------+---------------+------+---------+------+------+-------------
--------------------+
0.24 sec

So if I read it right, the engine runs down the item list once for each
supplier on the join query,
but it can optimise that on the sum query and do the sorting in one pass.

I'm probably talking crap though!

Thanks everyone for their help on this.

Ron