Optimize the SQL

October 1, 2009 at 19:40:23
Specs: Windows XP
I would like to know how to optimize this SQL to let my server load faster and take low usage?

I need to calculate the radius distance for a US ZIP Code to get the result, such as 50 miles from a particular ZIP Code, can getting how many other data ( e.g. ZIP Code ) from my database.
Once I get the result ( for example got 350 rows ), I need to passing to another query to count total rows and display it in simple and one result for me to read. Here is an example of my query:

SELECT count(*)
FROM
(SELECT b.ID, ROUND((acos(sin(3.142/180*32.91336) * sin(3.142/180*z.latitude) + cos(3.142/180*32.91336) * cos(3.142/180*z.latitude) * cos((3.142/180*z.longitude) - (3.142/180*-85.93836))) * 3959),2) AS distance
FROM zipcode2business.accountants b LEFT JOIN zipcodeworld.storelocator_us z ON b.ZIPCODE = z.ZIP_CODE
WHERE z.latitude != 32.91336 AND z.longitude != -85.93836
AND b.STATE='AL'
HAVING distance between 0 AND 50) as total;

Hopefully I didn't done wrongly, it displays correct result, but I need an optimized way as this SQL gave me a high CPU usage to load. When I do EXPLAIN for this query, it display following:
+----+-------------+-------+--------+------------------+---------+---------+----------------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------+---------+---------+----------------------------+------+------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | b | ref | ZIPCODE,STATE | STATE | 4 | | 3900 | Using where |
| 2 | DERIVED | z | eq_ref | PRIMARY,LAT_LONG | PRIMARY | 9 | zipcode2business.b.ZIPCODE | 1 | Using where |
+----+-------------+-------+--------+------------------+---------+---------+----------------------------+------+------------------------------+
3 rows in set (0.20 sec)

From above explaination, is that Select tables optimized away a good thing?
Please kindly show me one most perfect optimization SQL to do this query.


See More: Optimize the SQL

Report •


#1
October 16, 2009 at 19:13:37
I think it's best to take the computation out of the SQL. Let MySQL be the data store only. This way your application can scale. In other words, in the event that you need more computing power, you can add more servers. As opposed to having to load balance across several databases. Database replication and off loading becomes a concern.

Regards

Fully Managed Dedicated Servers - http://www.unixy.net/?page=dedicated
joe@unixy.net


Report •
Related Solutions


Ask Question