I am by no means an accomplished MySQL user, and generally deal with rather
simple queries having no need for anything particularly complex, until now.
I have been working on a query this past week which has tested my abilities
and when all is said and done, the query creates a rather hefty load on my
system, completely dominating my resources and taking a very long time to
produce results. I am hoping that some of the more experienced among you
might be able to help me find ways that I can optimize this query.
Here is an example of the query in action:
mysql> SELECT DISTINCT categories.name FROM categories, supplier,
suppliercatlink, zipcodes WHERE supplier.address_zip=zipcodes.zipcode AND
zipcodes.latitude > '32.80' AND zipcodes.latitude < '34.24' AND
zipcodes.longitude > '85.93' AND zipcodes.longitude < '87.67' AND
supplier.id = suppliercatlink.supid AND
categories.top_id!=categories.root_id AND categories.root_id=69;
+----------------------------+
| name |
+----------------------------+
| Interior Decorators |
| Interior Designers |
| Kitchen and Bath Designers |
+----------------------------+
3 rows in set (33.51 sec)
And what I get when I have it 'explain' the query:
mysql> explain
-> SELECT DISTINCT categories.name FROM categories, supplier,
suppliercatlink, zipcodes WHERE supplier.address_zip=zipcodes.zipcode AND
zipcodes.latitude > '32.80' AND zipcodes.latitude < '34.24' AND
zipcodes.longitude > '85.93' AND zipcodes.longitude < '87.67' AND
supplier.id = suppliercatlink.supid AND
categories.top_id!=categories.root_id AND categories.root_id=69;
+-----------------+--------+-----------------+----------+---------+---------
--------------+--------+------------------------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+-----------------+--------+-----------------+----------+---------+---------
--------------+--------+------------------------------+
| categories | ref | root_key | root_key | 4 | const
| 3 | Using where; Using temporary |
| suppliercatlink | index | supkey | supkey | 4 | NULL
| 194395 | Using index; Distinct |
| supplier | eq_ref | PRIMARY,zip_key | PRIMARY | 4 |
suppliercatlink.supid | 1 | Distinct |
| zipcodes | ref | zip_idx | zip_idx | 5 |
supplier.address_zip | 1 | Using where; Distinct |
+-----------------+--------+-----------------+----------+---------+---------
--------------+--------+------------------------------+
4 rows in set (0.00 sec)
This query is called by a web page which actually calls the query a number
of times based on results of another slightly less complex but similar
query. In most cases, this query is called at least 10 times when the page
is loaded and a single request tends to shoot the processor load of my
server up to about 80%+.
TIA,
Jason Drake
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
