Page 2 of 2

Re: Beast of a MySQL query, any suggestions?

Posted: Fri Jan 09, 2009 6:00 am
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 :)

Re: Beast of a MySQL query, any suggestions?

Posted: Fri Jan 09, 2009 6:01 am
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 ;)

Re: Beast of a MySQL query, any suggestions?

Posted: Fri Jan 09, 2009 6:31 am
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?

Re: Beast of a MySQL query, any suggestions?

Posted: Fri Jan 09, 2009 6:32 am
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?

Re: Beast of a MySQL query, any suggestions?

Posted: Fri Jan 09, 2009 7:30 am
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.

Re: Beast of a MySQL query, any suggestions?

Posted: Fri Jan 09, 2009 7:32 am
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]

Re: Beast of a MySQL query, any suggestions?

Posted: Fri Jan 09, 2009 8:05 am
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 :)

Re: Beast of a MySQL query, any suggestions?

Posted: Fri Jan 09, 2009 11:01 am
by Eran
After you do what Vlad suggested, run the EXPLAIN again and make sure your indexes are used

Re: Beast of a MySQL query, any suggestions?

Posted: Mon Jan 12, 2009 2:04 am
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: