Beast of a MySQL query, any suggestions?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Beast of a MySQL query, any suggestions?

Post by jaoudestudios »

VladSun wrote:Remove the CASE and use it inner condition in the WHERE clause.
inner condition in the WHERE? can you point me in the direction of an example please?
VladSun wrote:Also, you will see that some of the conditions are repeated or exclude each other, so optimize them :)
How do you know that? I am sure it is true, but from the EXPLAIN how can you tell? Thanks :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Beast of a MySQL query, any suggestions?

Post by VladSun »

[sql]SELECT     requirement.require_id,     requirement.title,     requirement.min_ft,     requirement.max_ft,     requirement.min_m,     requirement.max_m,     requirement.min_a,     requirement.max_a,     requirement.min_h,     requirement.max_h,     requirement.pdf,     requirement.date,     requirement.description,     CONCAT(requirement.min_ft, '-', requirement.max_ft, ' sqft, ', requirement.min_m, '-', requirement.max_m, ' meters, ', requirement.min_a, '-', requirement.max_a, ' acres, ', requirement.min_h, '-', requirement.max_h, ' hectares') AS sizeRange,     retailer.account_type,     retailer.name AS rName,     retailer.retailer_id,     retailer.logo,     data_planning_type.planning_id,     data_planning_type.class,     data_planning_type.type,     GROUP_CONCAT(DISTINCT data_planning_type.class SEPARATOR ', ') AS planType,     GROUP_CONCAT(DISTINCT require_region_inc.region_id SEPARATOR ', ') AS location,     users.firstname,     users.lastname,     users.company FROM     requirement JOIN     retailer ON requirement.retailer_id = retailer.retailer_id LEFT JOIN     require_planning ON requirement.require_id = require_planning.require_id LEFT JOIN     data_planning_type ON require_planning.planning_id = data_planning_type.planning_id JOIN     require_region_inc ON requirement.require_id = require_region_inc.require_id LEFT JOIN     require_region_exc ON requirement.require_id = require_region_exc.require_id JOIN     data_regions_uk ON require_region_inc.region_id = data_regions_uk.id JOIN     user_requirement ON requirement.require_id = user_requirement.require_id JOIN     users ON user_requirement.user_id = users.user_id WHERE       (data_planning_type.planning_id = '1')     AND     (require_region_inc.region_id = '24517' AND require_region_inc.depth = 3)     AND      (requirement.description LIKE '%a%' OR requirement.title LIKE '%a%')     AND      (requirement.min_ft < 1000 AND requirement.max_ft > 1000)     AND      (users.firstname LIKE '%a%' OR users.lastname LIKE '%a%')     AND      (users.company LIKE '%a%')     AND      (retailer.name LIKE '%a%')    AND    !(        (require_region_exc.region_id = '24517' AND require_region_exc.depth = '3')         OR         (require_region_exc.region_id = data_regions_uk.id AND data_regions_uk.county = 'Surrey' AND require_region_exc.depth = '2')         OR         (require_region_exc.region_id = data_regions_uk.id AND data_regions_uk.region = 'South East' AND require_region_exc.depth = '1')     )GROUP BY    requirement.require_id ORDER BY     retailer.account_type ASC,     requirement.date DESC[/sql]

Look at the WHERE clause ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Beast of a MySQL query, any suggestions?

Post by jaoudestudios »

ah, I see what you mean by inner condition in the WHERE clause, I thought you were referring to some funky feature I was unaware of - sorry I was over complicating the matter without looking at the obvious :banghead:

Does the EXPLAIN info give you any OPTIMISATION information/ideas?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Beast of a MySQL query, any suggestions?

Post by jaoudestudios »

ah, I see what you mean by inner condition in the WHERE clause, I thought you were referring to some funky feature I was unaware of - sorry I was over complicating the matter without looking at the obvious :banghead:

Does the EXPLAIN info give you any OPTIMISATION information/ideas?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Beast of a MySQL query, any suggestions?

Post by VladSun »

You should have indexes built for all fields which are found in
- JOIN ON conditions;
- WHERE conditions;
- ORDER BY clause;
- optionally for GROUP BY clause;

Also, I don't' know where these:
[sql]AND data_regions_uk.county = 'Surrey'[/sql]come from but try to avoid string search.
Last edited by VladSun on Fri Jan 09, 2009 8:08 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Beast of a MySQL query, any suggestions?

Post by VladSun »

jaoudestudios wrote:To run the query with no modifications suggested it takes 0.07-0.08secs (development machine). This query & results is probably cached, how can I clear the cache to do a real test?
[sql]SELECT SQL_NO_CACHE ...[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Beast of a MySQL query, any suggestions?

Post by jaoudestudios »

VladSun wrote:
jaoudestudios wrote:To run the query with no modifications suggested it takes 0.07-0.08secs (development machine). This query & results is probably cached, how can I clear the cache to do a real test?
[sql]SELECT SQL_NO_CACHE ...[/sql]
Thanks. It took the same time. Good news I guess.

I will try and remove the string search, might not be possible though.

Thanks for all your help and suggestions VladSun :)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Beast of a MySQL query, any suggestions?

Post by Eran »

After you do what Vlad suggested, run the EXPLAIN again and make sure your indexes are used
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Beast of a MySQL query, any suggestions?

Post by jaoudestudios »

Got side track with other work, however, I have done a lot of research into the suggestions made. I really did not realise how bad it was to use SQL_CALC_FOUND_ROWS.

Getting back on it today, hopefully if nothing else comes up. :wink:
Post Reply