MySQL Forums
Forum List  »  Performance

1 Minute to Examine 2907 Rows ?!?!?!?
Posted by: Brett Berry
Date: June 03, 2005 11:13AM

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) |
+----+-------------+------------+-------+---------------+------------+---------+--------+------+------------------------------------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
1 Minute to Examine 2907 Rows ?!?!?!?
2140
June 03, 2005 11:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.