Hi,
I want search a string in elgg_users_entity and elgg_metadata tables. Please check the sql what i have written. But it take lot of time. Is it right?
Sql:
SELECT DISTINCT * FROM elgg_entities e
JOIN elgg_metadata md on e.guid = md.entity_guid
JOIN elgg_metastrings msn on md.name_id = msn.id
JOIN elgg_metastrings msv on md.value_id = msv.id
JOIN elgg_users_entity ue ON e.guid = ue.guid
WHERE (msn.string IN ("skills","skills1")
AND msv.string = 'php' OR (ue.username LIKE '%php%' OR ue.name LIKE '%php%')
AND ( (1 = 1) and md.enabled='yes' OR (md.owner_guid = 2 AND md.enabled='no')))
AND ((e.type = 'user')) AND (e.site_guid IN (1))
info@elgg.org
Security issues should be reported to security@elgg.org!
©2014 the Elgg Foundation
Elgg is a registered trademark of Thematic Networks.
Cover image by RaĆ¼l Utrera is used under Creative Commons license.
Icons by Flaticon and FontAwesome.
- Juho Jaakkola@juho.jaakkola
Juho Jaakkola - 0 likes
- kirubakaran k@kirubakarank
kirubakaran k - 0 likes
You must log in to post replies.It looks like a valid query. Multiple JOINs just have the tendency of slowing down queries.
You could try optimizing it by using separate queries to first get the ids for the metastrings with elgg_get_metastring_id($string). Then you could leave out both JOINs to the metastrings table.
Thank you juho.