I wrote this query a month or so ago, it is not optimised as there was not time, it was in my previous role just before I left. But I am proud of the sheer size of the query.
I posted it on
http://www.devnetwork.net where I am a
devnet resident and I got some good feedback on how to optimise it. Unfortunately I was unable to attempt to optimise it as now I have left that role I do not have the database to run the query against.
Here is the query and below that is the link to the forum thread on Devnetwork.
Code:
SELECT
SQL_CALC_FOUND_ROWS
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
, (CASE WHEN ((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')
) THEN -1 ELSE 0 END) AS notLocation
, 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%')
GROUP BY
requirement.require_id
HAVING notLocation = 0
ORDER BY retailer.account_type ASC, requirement.date DESC
LIMIT 0,20
http://forums.devnetwork.net/viewtopic.php?f=2&t=93243&hilit=+beast
Login
Register
FAQ
Search
