Query optimization

[vssp]

Hai friends

previously I have optimized the query using index that time in my database only 65000 record ,right now in my database 1,50000 recoreds so i need again optimize the query .Now my site loaded very slow . Please give me any suggestion how to optiminze the query agian .

Thanks
vssp

[chazzy]

have you attempted to do an explain on the query...?

[vssp]

here is the query , I have already used index key bit now it take 20 sec to execute the query any idea how to reduce the execution time ????
Thanks
vssp



SELECT o.id
FROM tbl_organ o USE INDEX(name,name_alternate,website,language_master_id),
organizations_master om,organization_status_master osm,
organizations_locations ol,
countries_master cm
WHERE (o.language_master_id = om.native_language_id AND o.organization_master_id = om.id AND osm.id = o.active_status AND ol.country_master_id = cm.id
AND ol.organization_master_id = o.organization_master_id)
AND (name REGEXP "Int((re|er)|er)national A((z|`s|'s|s)|`s|'s|s)((z|`s|'s|s)|`s|'s|s)ociation for En((re|er)|er)gy Economic((z|`s|'s|s)|`s|'s|s)" || name_alternate REGEXP "Int((re|er)|er)national A((z|`s|'s|s)|`s|'s|s)((z|`s|'s|s)|`s|'s|s)ociation for En((re|er)|er)gy Economic((z|`s|'s|s)|`s|'s|s)" || website REGEXP "Int((re|er)|er)national A((z|`s|'s|s)|`s|'s|s)((z|`s|'s|s)|`s|'s|s)ociation for En((re|er)|er)gy Economic((z|`s|'s|s)|`s|'s|s)")
GROUP BY o.organization_master_id
ORDER BY name ASC;