Mysql Error: Query execution was interrupted

My site is slow in parts especially when i use the search function on my site at www.localbizit.com - I get the following error every 3rd or 4th search which according to my dev team cannot be resolved because the querry needs to filter through 4.5 million records in my mysql DB.

Granted it is on a shared hosting account but I will be moving it to a VPS and should this improve the response of the querry?

I am open to any help you can provide to solve this issue:

Error: Query execution was interrupted

SELECT SQL_CALC_FOUND_ROWS t1.*, t44.`path` path, `t44`.`title` `category_title`, (3956 * 2 * ASIN(SQRT( POWER(SIN((39.954582 - tz.Latitude) * 0.0174532925 / 2), 2) + COS(39.954582 * 0.0174532925) * COS(tz.Latitude * 0.0174532925) * POWER(SIN((-74.996843 - tz.Longitude) * 0.0174532925 / 2), 2) ))) as `distance` FROM `v2303_listings` t1 LEFT JOIN `ZIPCodes` tz ON SUBSTR(t1.`zip`,1,5) = tz.`ZIPCode` LEFT JOIN `v2303_categories` t44 ON t44.`id` = t1.`category_id` WHERE `t1`.`status` = 'active' AND CONCAT(t1.`title`, t1.`description`) LIKE '%pizza%'AND t1.zip > '0' GROUP BY t1.`id` having `distance` <= 30 ORDER BY t1.`featured` DESC, `distance` ASC LIMIT 0, 10


Debug backtrace:
#0 esynDatabase->query(SELECT SQL_CALC_FOUND_ROWS t1.*, t44.`path` path, `t44`.`title` `category_title`, (3956 * 2 * ASIN(SQRT(

POWER(SIN((39.954582 - tz.Latitude) * 0.0174532925 / 2), 2) +
COS(39.954582 * 0.0174532925) *
COS(tz.Latitude * 0.0174532925) *
POWER(SIN((-74.996843 - tz.Longitude) * 0.0174532925 / 2), 2)
))) as `distance` FROM `v2303_listings` t1 LEFT JOIN `ZIPCodes` tz ON SUBSTR(t1.`zip`,1,5) = tz.`ZIPCode` LEFT JOIN `v2303_categories` t44 ON t44.`id` = t1.`category_id` WHERE `t1`.`status` = 'active' AND CONCAT(t1.`title`, t1.`description`) LIKE '%pizza%'AND t1.zip > '0'


GROUP BY t1.`id` having `distance` <= 30 ORDER BY t1.`featured` DESC, `distance` ASC LIMIT 0, 10) called at [/home/localbiz/public_html/includes/classes/esynDatabase.php:252]
#1 esynDatabase->getAll(SELECT SQL_CALC_FOUND_ROWS t1.*, t44.`path` path, `t44`.`title` `category_title`, (3956 * 2 * ASIN(SQRT(

POWER(SIN((39.954582 - tz.Latitude) * 0.0174532925 / 2), 2) +
COS(39.954582 * 0.0174532925) *
COS(tz.Latitude * 0.0174532925) *
POWER(SIN((-74.996843 - tz.Longitude) * 0.0174532925 / 2), 2)
))) as `distance` FROM `v2303_listings` t1 LEFT JOIN `ZIPCodes` tz ON SUBSTR(t1.`zip`,1,5) = tz.`ZIPCode` LEFT JOIN `v2303_categories` t44 ON t44.`id` = t1.`category_id` WHERE `t1`.`status` = 'active' AND CONCAT(t1.`title`, t1.`description`) LIKE '%pizza%'AND t1.zip > '0'


GROUP BY t1.`id` having `distance` <= 30 ORDER BY t1.`featured` DESC, `distance` ASC LIMIT 0, 10) called at [/home/localbiz/public_html/plugins/search_by_distance/includes/classes/esynSearchByDistance.php:47]
#2 esynSearchByDistance->getSearchLinksByDistance(Array ([what] => pizza,[zip] => 08052,[miles] => 30), 0, 10) called at [/home/localbiz/public_html/search.php:191]
#3 injectSearchClause123() called at [/home/localbiz/public_html/search.php:216]
--------------------------------------------------------------------------------

Database query error: Error: Query execution was interruptedSELECT SQL_CALC_FOUND_ROWS t1.*, t44.`path` path, `t44`.`title` `category_title`, (3956 * 2 * ASIN(SQRT( POWER(SIN((39.954582 - tz.Latitude) * 0.0174532925 / 2), 2) + COS(39.954582 * 0.0174532925) * COS(tz.Latitude * 0.0174532925) * POWER(SIN((-74.996843 - tz.Longitude) * 0.0174532925 / 2), 2) ))) as `distance` FROM `v2303_listings` t1 LEFT JOIN `ZIPCodes` tz ON SUBSTR(t1.`zip`,1,5) = tz.`ZIPCode` LEFT JOIN `v2303_categories` t44 ON t44.`id` = t1.`category_id` WHERE `t1`.`status` = 'active' AND CONCAT(t1.`title`, t1.`description`) LIKE '%pizza%'AND t1.zip > '0' GROUP BY t1.`id` having `distance` query(SELECT SQL_CALC_FOUND_ROWS t1.*, t44.`path` path, `t44`.`title` `category_title`, (3956 * 2 * ASIN(SQRT( POWER(SIN((39.954582 - tz.Latitude) * 0.0174532925 / 2), 2) + COS(39.954582 * 0.0174532925) * COS(tz.Latitude * 0.0174532925) * POWER(SIN((-74.996843 - tz.Longitude) * 0.0174532925 / 2), 2) ))) as `distance` FROM `v2303_listings` t1 LEFT JOIN `ZIPCodes` tz ON SUBSTR(t1.`zip`,1,5) = tz.`ZIPCode` LEFT JOIN `v2303_categories` t44 ON t44.`id` = t1.`category_id` WHERE `t1`.`status` = 'active' AND CONCAT(t1.`title`, t1.`description`) LIKE '%pizza%'AND t1.zip > '0' GROUP BY t1.`id` having `distance` getAll(SELECT SQL_CALC_FOUND_ROWS t1.*, t44.`path` path, `t44`.`title` `category_title`, (3956 * 2 * ASIN(SQRT( POWER(SIN((39.954582 - tz.Latitude) * 0.0174532925 / 2), 2) + COS(39.954582 * 0.0174532925) * COS(tz.Latitude * 0.0174532925) * POWER(SIN((-74.996843 - tz.Longitude) * 0.0174532925 / 2), 2) ))) as `distance` FROM `v2303_listings` t1 LEFT JOIN `ZIPCodes` tz ON SUBSTR(t1.`zip`,1,5) = tz.`ZIPCode` LEFT JOIN `v2303_categories` t44 ON t44.`id` = t1.`category_id` WHERE `t1`.`status` = 'active' AND CONCAT(t1.`title`, t1.`description`) LIKE '%pizza%'AND t1.zip > '0' GROUP BY t1.`id` having `distance` getSearchLinksByDistance(Array ([what] => pizza,[zip] => 08052,[miles] => 30), 0, 10) called at [/home/localbiz/public_html/search.php:191] #3 injectSearchClause123() called at [/home/localbiz/public_html/search.php:216]


ThanxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Moving to a VPS will help performance, probably.

From a quick glance at the query it looks like you are trying to implement the Haversine formula in a MySQL query.  Don't do that.  Instead, please step back from the technical details of this question and tell us in plain language what you're trying to achieve - for example, "I want to find the nearest McDonalds."  If we have that information we may be able to suggest application design patterns that are tested and known to work.  Thanks, ~Ray
0
elliottwebsitesCommented:
Moving to a VPS will improve your speed, but its got to search through 4.5 million records.
Maybe have your dev team work on some really good caching system?
Have the most common queries stored in a flat file?
0
ThanxAuthor Commented:
Ray_Paseur:

thanks for responding - the entire focus of my site is to find local businesses in your area - sort of a local yellow pages for businesses - this means if you do a search for pizza the results should display the closest pizza places to your default set location which is autosensed when you goto the site via google Geo location api.

if there are no pizza places in the db in your tyown i want it to show the next closest towns pizza places to your location and so on and so on.

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Beverley PortlockCommented:
Are you sure that every column in a WHERE, ON or ORDER BY clause has an index in the database?

SELECT SQL_CALC_FOUND_ROWS t1.*, t44.`path` path, `t44`.`title` `category_title`, (3956 * 2 * ASIN(SQRT( POWER(SIN((39.954582 - tz.Latitude) * 0.0174532925 / 2), 2) + COS(39.954582 * 0.0174532925) * COS(tz.Latitude * 0.0174532925) * POWER(SIN((-74.996843 - tz.Longitude) * 0.0174532925 / 2), 2) ))) as `distance` FROM `v2303_listings` t1 LEFT JOIN `ZIPCodes` tz ON SUBSTR(t1.`zip`,1,5) = tz.`ZIPCode` LEFT JOIN `v2303_categories` t44 ON t44.`id` = t1.`category_id` WHERE `t1`.`status` = 'active' AND CONCAT(t1.`title`, t1.`description`) LIKE '%pizza%'AND t1.zip > '0' GROUP BY t1.`id` having `distance` <= 30 ORDER BY t1.`featured` DESC, `distance` ASC LIMIT 0, 10


I would also look at changing the database structure slightly. Consider splitting the ZIP code into two columns, one column 5 chars in length and the rest of teh zip code in the other column. That would eliminate the SUBSTR operation. If you do not want to disturb the existing ZIP column then create a new one called (say) ZIP5 and ensure it contains the first 5 characters of the ZIP and make sure it is indexed and use it where you have the existing SUBSTR operation.
0
Ray PaseurCommented:
This article teaches the principles of what you're trying to do, and it will probably result in a faster response from the query.  It uses a down-select to locate only the nearby items, then computes the distances from your start point to the nearby items and orders the items by closest first.  Since the pattern recomputes the distance for each set of items, it is usable with a mobile device, where the exact starting point may not be known until script execution.  Read it over and post back with any specific questions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html

Best regards, ~Ray
0
Ray PaseurCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ThanxAuthor Commented:
ok but how will splitting the database help when it would still be going through 4.5 million records and still using the LIKE statement?
0
Ray PaseurCommented:
Using the LIKE statement? Why would you do that?  Why not just follow the design pattern illustrated in the article?
0
Beverley PortlockCommented:
"ok but how will splitting the database help when it would still be going through 4.5 million records and still using the LIKE statement?"

By splitting the ZIP code field you eliminate the costly substr operation that has to be performed on every record and by putting an index on the new shorter field you give MySQL a way to eliminate all the records from the 4.5 million that do not match a given partial zip code.

I'm willing to bet that using a shortened ZIP index will eliminate 99% of the database. Since your logical connector is an AND rather than an OR it means that the LIKE will be applied to a small subset and not to the entire database. MySQL will always try and use the most restrictive index to reduce the data set to the smallest size as quickly as possible.

Why theorize? You can simply create a new column based on ZIP 1..5, index it and alter the query above to use it and measure if it runs any quicker. Such an experiment should not upset the data table and would take less than an hour to set up, run and evalute. It would be even better if you had a test server that you could run this on without affecting the live machine at all.
0
Ray PaseurCommented:
ZIP codes are not locations - they are postal carriers' routes.  In densely populated areas like Manhattan they may be as small as a building or two; in the US west they may span hundreds of miles.  So they are not particularly accurate as a "pinpoint" location, but they are an acceptable proxy for at least the client's point of origin.  If you ask the client for a street address, you can use the street address with one of the geocoder services, and that will give you greater accuracy.  I am assuming here that you would know the accurate geolocation of the client's destination, or at least the expected location of the client's IP address.

I live in McLean, VA, so I searched for "McLean, VA Restaurants" and the search took 22 seconds to complete.  And when it completed, it gave me only two suggestions, both for the same restaurant many, many miles from my home (and in a dismal part of town).  A search for Post Offices was also very slow, but it found my local post office.  However it also listed many post offices that were long distances away -- so it was accurate at finding the closest post office, but it also returned several pages of irrelevant results.  This suggests to me that a LIMIT clause would speed up the query.

It may be that the query is causing the slow response and weak accuracy, or that the data model simply does not contain enough meaningful information to generate good results.  A Google search for "McLean, VA Restaurants" completed in sub-second time with highly accurate results.  So, two questions...

Where did the 4.5 million rows of data come from (it might be good to see the CREATE TABLE statement)?

Have you tried the design pattern in the article?
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html

0
ThanxAuthor Commented:
I have asked my developer to look at this suggestion and try to impliment it. I will reply when he has done so.
0
ThanxAuthor Commented:
I am still waiting for a reply from my Developer and again will reply when he does.

thanks
0
ThanxAuthor Commented:
Ok my developers said they have tried the suggestion but it made no improvements in the speed of the query. Thats all they will really say.

They are claiming that my 4.5 million records are a lot to go through for the query and thats why its a bit slow - so i guess i need to remove about 1 million records and see how the performance results.

Unless anyone has any other suggestion?

Thanks everyone for your help.

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.