1 Minute to Examine 2907 Rows ?!?!?!?
Query Browser, MySQL Prompt, PHP Executed Query are all slower than the MySQL Control Center!!!
Anyone have any idea why a complex query run in the MySQL Control Center only takes 2.42 seconds, while the same query run from the MySQL prompt in a dos window takes 59.52 seconds?
The MySQL Query Browser takes just as long as the MySQL prompt.
Why does it take a frickin minute to examine 2907 rows?
Here is the entry from the slow log:
# Time: 050603 10:02:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 59 Lock_time: 0 Rows_sent: 1426 Rows_examined: 2907
select
j.lic_number,j.sw_prod,j.quantity_assigned,j.seats,r.area_name,
r.area_poc,h.host_name,h.host_number,h.host_poc,j.owner,
j.purchased_by, j.key_location,j.ser_number,j.location_of_media,
j.cmvc_archive_loc,j.license_id
from
area as r,host as h,
(select l.lic_number, concat_ws(': ',v.company_name,p.software_name,s.version_name) as sw_prod,
a.quantity_assigned,l.seats,l.owner,l.purchased_by,l.key_location,l.ser_number, s.location_of_media,
s.cmvc_archive_loc,concat(l.serv_id,l.uid) as license_id, a.area_id,a.host_id
from
sw_vendor as v,sw_product as p,sw_version as s,license as l,assigned as a
where p.vendor_id=concat(v.serv_id,v.uid)
and s.product_id=concat(p.serv_id,p.uid)
and l.version_id=concat(s.serv_id,s.uid)
and a.license_id=concat(l.serv_id,l.uid)
and l.valid=1 and s.valid=1 and p.valid=1 and v.valid=1) as j
where j.area_id=concat(r.serv_id,r.uid)
and j.host_id=concat(h.serv_id,h.uid)
order by j.sw_prod,j.lic_number,j.quantity_assigned;
Here is the output of the explain.
+----+-------------+------------+-------+---------------+------------+---------+--------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------------+---------+--------+------+------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 1426 | Using temporary; Using filesort |
| 1 | PRIMARY | r | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 55 | Using where |
| 1 | PRIMARY | h | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 479 | Using where |
| 2 | DERIVED | v | ALL | valid | [NULL] | [NULL] | [NULL] | 61 | Using where |
| 2 | DERIVED | p | range | vendor_id | vendor_id | 7 | [NULL] | 321 | Range checked for each record (index map: 0x2) |
| 2 | DERIVED | s | range | product_id | product_id | 10 | [NULL] | 358 | Range checked for each record (index map: 0x2) |
| 2 | DERIVED | l | range | version | version | 9 | [NULL] | 1463 | Range checked for each record (index map: 0x4) |
| 2 | DERIVED | a | range | license_id | license_id | 10 | [NULL] | 1426 | Range checked for each record (index map: 0x1) |
+----+-------------+------------+-------+---------------+------------+---------+--------+------+------------------------------------------------+