I followed the instructions from This link
It seems that it doesn't improve much(only 0.09 Sec improved). Is not enough, My aim is to optimize it more.
Tables
state: id,title,abbr
regions: id,title
regions_suburbs: region_id,suburb_id
suburbs: id,state_id,region_id,postcode
properties: id,title
I am trying to find any keyword inside proprety.title, suburb.title, state.abbr, state.title and suburb.postcode.
Query as below-
[Perivous] Showing rows 0 - 4 (5 total, Query took 4.7122 sec)
SELECT * FROM (SELECT CASE WHEN p.id IS NOT NULL THEN CONCAT('project_id|',p.id) WHEN s.id IS NOT NULL THEN CONCAT('suburb_id|',s.id) ELSE '0' END AS id,s.title As SuburbName,s.postcode,st.abbr,CONCAT(CASE WHEN p.propertyname IS NULL THEN '' ELSE CONCAT(p.propertyname,', ') END,CASE WHEN s.title IS NULL THEN'' ELSE CONCAT(UPPER(s.title), ' ') END,CASE WHEN st.abbr IS NULL THEN '' ELSE CONCAT(UPPER(st.abbr), ' ') END,CASE WHEN s.postcode IS NULL THEN '' ELSE CONCAT(s.postcode, '') END) AS SearchTerm FROM properties p LEFT OUTER JOIN suburbs s ON p.suburb_id=s.id LEFT JOIN regions_suburbs rs ON rs.suburb_id=s.id LEFT JOIN regions r ON rs.region_id=r.id LEFT JOIN state st ON st.id=s.state_id UNION ALL SELECT CASE WHEN p.id IS NOT NULL THEN CONCAT('project_id|',p.id) WHEN s.id IS NOT NULL THEN CONCAT('suburb_id|',s.id) ELSE '0' END AS id,s.title As SuburbName,s.postcode,st.abbr,CONCAT(CASE WHEN p.propertyname IS NULL THEN '' ELSE CONCAT(p.propertyname,', ') END,CASE WHEN s.title IS NULL THEN'' ELSE CONCAT(UPPER(s.title), ' ') END,CASE WHEN st.abbr IS NULL THEN '' ELSE CONCAT(UPPER(st.abbr), ' ') END,CASE WHEN s.postcode IS NULL THEN '' ELSE CONCAT(s.postcode, '') END) AS SearchTerm FROM properties p RIGHT OUTER JOIN suburbs s ON p.suburb_id=s.id LEFT JOIN regions_suburbs rs ON rs.suburb_id=s.id LEFT JOIN regions r ON rs.region_id=r.id LEFT JOIN state st ON st.id=s.state_id UNION ALL SELECT CONCAT('state_id|',id),'' As SuburbName,'' AS postcode,abbr,title AS SearchTerm FROM state) AS U WHERE 1 AND (SuburbName LIKE 'Newtown%' OR postcode='Newtown' OR LOWER(SearchTerm) LIKE LOWER('Newtown%') OR abbr LIKE 'Newtown%') ORDER BY SearchTerm ASC LIMIT 0,10
EXPLAIN RESULT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16657 Using where; Using filesort
2 DERIVED p ALL NULL NULL NULL NULL 3
2 DERIVED s eq_ref PRIMARY PRIMARY 4 residential.p.suburb_id 1
2 DERIVED rs ALL NULL NULL NULL NULL 383
2 DERIVED r eq_ref PRIMARY PRIMARY 4 residential.rs.region_id 1 Using index
2 DERIVED st eq_ref PRIMARY PRIMARY 4 residential.s.state_id 1
3 UNION s ALL NULL NULL NULL NULL 16640
3 UNION p ALL NULL NULL NULL NULL 3
3 UNION rs ALL NULL NULL NULL NULL 383
3 UNION r eq_ref PRIMARY PRIMARY 4 residential.rs.region_id 1 Using index
3 UNION st eq_ref PRIMARY PRIMARY 4 residential.s.state_id 1
4 UNION state ALL NULL NULL NULL NULL 8
NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL
[Now] Showing rows 0 - 4 (5 total, Query took 4.6246 sec)
SELECT CASE WHEN p.id IS NOT NULL THEN CONCAT('project_id|',p.id) WHEN s.id IS NOT NULL THEN CONCAT('suburb_id|',s.id) ELSE '0' END AS id,s.title As SuburbName,s.postcode,st.abbr,CONCAT(CASE WHEN p.propertyname IS NULL THEN '' ELSE CONCAT(p.propertyname,', ') END,CASE WHEN s.title IS NULL THEN'' ELSE CONCAT(UPPER(s.title), ' ') END,CASE WHEN st.abbr IS NULL THEN '' ELSE CONCAT(UPPER(st.abbr), ' ') END,CASE WHEN s.postcode IS NULL THEN '' ELSE CONCAT(s.postcode, '') END) AS SearchTerm FROM properties p LEFT OUTER JOIN suburbs s ON p.suburb_id=s.id LEFT JOIN regions_suburbs rs ON rs.suburb_id=s.id LEFT JOIN regions r ON rs.region_id=r.id LEFT JOIN state st ON st.id=s.state_id WHERE 1 AND (s.title LIKE 'Newtown%' OR postcode='Newtown' OR LOWER(CONCAT(CASE WHEN p.propertyname IS NULL THEN '' ELSE CONCAT(p.propertyname,', ') END,CASE WHEN s.title IS NULL THEN'' ELSE CONCAT(UPPER(s.title), ' ') END,CASE WHEN st.abbr IS NULL THEN '' ELSE CONCAT(UPPER(st.abbr), ' ') END,CASE WHEN s.postcode IS NULL THEN '' ELSE CONCAT(s.postcode, '') END)) LIKE LOWER('Newtown%') OR st.abbr LIKE 'Newtown%') UNION ALL SELECT CASE WHEN p.id IS NOT NULL THEN CONCAT('project_id|',p.id) WHEN s.id IS NOT NULL THEN CONCAT('suburb_id|',s.id) ELSE '0' END AS id,s.title As SuburbName,s.postcode,st.abbr,CONCAT(CASE WHEN p.propertyname IS NULL THEN '' ELSE CONCAT(p.propertyname,', ') END,CASE WHEN s.title IS NULL THEN'' ELSE CONCAT(UPPER(s.title), ' ') END,CASE WHEN st.abbr IS NULL THEN '' ELSE CONCAT(UPPER(st.abbr), ' ') END,CASE WHEN s.postcode IS NULL THEN '' ELSE CONCAT(s.postcode, '') END) AS SearchTerm FROM properties p RIGHT OUTER JOIN suburbs s ON p.suburb_id=s.id LEFT JOIN regions_suburbs rs ON rs.suburb_id=s.id LEFT JOIN regions r ON rs.region_id=r.id LEFT JOIN state st ON st.id=s.state_id WHERE 1 AND (s.title LIKE 'Newtown%' OR postcode='Newtown' OR LOWER(CONCAT(CASE WHEN p.propertyname IS NULL THEN '' ELSE CONCAT(p.propertyname,', ') END,CASE WHEN s.title IS NULL THEN'' ELSE CONCAT(UPPER(s.title), ' ') END,CASE WHEN st.abbr IS NULL THEN '' ELSE CONCAT(UPPER(st.abbr), ' ') END,CASE WHEN s.postcode IS NULL THEN '' ELSE CONCAT(s.postcode, '') END)) LIKE LOWER('Newtown%') OR st.abbr LIKE 'Newtown%') UNION ALL SELECT CONCAT('state_id|',id),'' As SuburbName,'' AS postcode,abbr,title AS SearchTerm FROM state WHERE 1 AND title LIKE 'Newtown%' ORDER BY SearchTerm ASC LIMIT 0,10
EXPLAIN RESULT
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY p ALL NULL NULL NULL NULL 3
1 PRIMARY s eq_ref PRIMARY PRIMARY 4 residential.p.suburb_id 1
1 PRIMARY rs ALL NULL NULL NULL NULL 383
1 PRIMARY r eq_ref PRIMARY PRIMARY 4 residential.rs.region_id 1 Using index
1 PRIMARY st eq_ref PRIMARY PRIMARY 4 residential.s.state_id 1 Using where
2 UNION s ALL title_postcode NULL NULL NULL 16640
2 UNION p ALL NULL NULL NULL NULL 3
2 UNION rs ALL NULL NULL NULL NULL 383
2 UNION r eq_ref PRIMARY PRIMARY 4 residential.rs.region_id 1 Using index
2 UNION st eq_ref PRIMARY PRIMARY 4 residential.s.state_id 1 Using where
3 UNION state range title title 102 NULL 1 Using where
NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL Using filesort
Thank you all for your help here. I finally found the culprit that slown down my query.
I tried to remove bits by bits from my SQL statement and run it over and over. Finally after removing the following two LEFT JOIN parts,
LEFT JOIN regions_suburbs rs
ON rs.suburb_id = s.id
LEFT JOIN regions r
ON rs.region_id = r.id
it improves from
Showing rows 0 - 1 (2 total, Query took 4.8538 sec)
to
Showing rows 0 - 1 (2 total, Query took 0.2337 sec)
To acheive this I had to make some changes to databsae design. Initally, property table stores only suburb_id because we can get its region_id and state_id if we know suburb_id. To avoid joining regions_suburbs and regions table back, I am now storing state_id, region_id and suburb_id instead of only one suburb_id. In this we could take out two LEFT JOIN from the query and it improves the query response time tremendously by leveraging two additional column storage which is worth.
EDIT: Creating some INDEX on the columns I am selecting grately improved the response time too.