How to reduce MySQL query execution time from 90 seconds to less than 5 seconds or even one second

Question

I am using MySQL database. When I execute a query which the main table(20 thousands of rows) is joined with the other 5 tables, it takes 90 seconds to return me the result. I then added indices into each of the criteria(where clause). Then the execution time drops from 90 seconds to 45 seconds and then I tried to select only necessary fields instead of all(*), it takes 25 seconds. What should I do to make it drop to less than 5 seconds or even one second?

SELECT
ap.propertyid, ap.type_market, 
ap.market_sale_price, ap.sale_from, ap.sale_until, ap.property_salepsf, 
ap.rent_from, ap.property_rent_until, ap.property_rentpsf, ap.title, 
ap.street, ap.suburbs, ap.state, ap.bedrooms, ap.bathrooms, ap.carport, 
ap.buildup_area, ap.builtup_area_from, ap.builtup_area_to, 
ap.land_area, ap.land_area_from, ap.land_area_to, 
ap.status AS prop_status, ap.datecreate AS uploadedOn, type_property.name, (
 SELECT name 
 FROM gallery 
 WHERE
 propertyid = ap.propertyid
 ORDER BY frontpage
 LIMIT 0, 1
) AS picture, 
uom_mst.uom_shortcode,
agent_profile.person, agent_profile.mobile, agent_profile.electronicmail, agent_profile.agent_pix, agent_profile.agent_pix_crop,
country.country_currency_html AS currency,
owner_profile.oname, owner_profile.omobile, owner_profile.oemail
FROM agentproperty ap
LEFT JOIN agent_profile ON (ap.agentid = agent_profile.agentid)
LEFT JOIN type_property ON (ap.type_property = type_property.rid)
LEFT JOIN uom_mst ON (ap.property_bua_uom = uom_mst.uom_id)
LEFT JOIN country ON (ap.property_ctry = country.country_id)
LEFT JOIN owner_profile ON (ap.propertyid = owner_profile.propertyid)
WHERE
ap.status = 'active' AND 
agent_profile.status = 'active' AND 
ap.property_type = '1' AND 
getCompanyID(ap.propertyid) = '001' AND 
ap.agentid = '100010001'
GROUP BY ap.propertyid
ORDER BY ap.datecreate DESC
LIMIT 200, 10

Answer 1

Assuming a high selectivity with agentid and propertyid (agent_property, agent_profile) and low selectivities for status, property_types, country_id etc) [meaning that there are many different agentids and propertyids but few different states, property type etc]

IMHO the indexes with the most impact would be:

• agent_property(agentid) or agent_property(agentid, property_type, status) - used in your where clause
• agent_profile(agentid) - used in joins
• owner_profile(propertyid)

However, you will also need to consider the impact of getCompanyId You haven't given the SQL but it will probably need at least an index of property(property_id) or whatever the property table is.

Answer 2

Run EXPLAIN PLAN on your query and see what MySQL says about it.

Make sure that all the columns involved in the JOINs have indexes associated with them.

Six tables in a JOIN is a lot of work for a database. I'd recommend seeing if reordering the JOINs has any effect. If you order the JOINs from most restrictive to least you might cut down on the work needed.