I'm trying to use the gem to filter out a list of customer which could have any of the given tag. The built in query from the gem provide this query result:
SELECT `meta_customers`.* FROM `meta_customers` WHERE EXISTS (
SELECT * FROM `taggings` WHERE `taggings`.`taggable_id` = `meta_customers`.`id` AND
`taggings`.`taggable_type` = 'MetaCustomer' AND
`taggings`.`tag_id` IN (
SELECT `tags`.`id` FROM `tags` WHERE (`tags`.`name` LIKE 'nike' ESCAPE '!' OR `tags`.`name` LIKE 'adidas' ESCAPE '!')) AND
`taggings`.`context` = 'preferred_brands' limit 1
)
This is really slow (~12 secs) has we have more than a million of customers. I'm not really familiar with EXISTS but running EXPLAIN showcase that no index is then used on the MetaCustomer where query
I did a small tweak so the subquery return ids instead to avoid using exist and rely on the primary key index. The result run in 78ms for the same result.
Is anyone having the same issue? Am I missing an index ?
Hi there,
I'm trying to use the gem to filter out a list of customer which could have any of the given tag. The built in query from the gem provide this query result:
MetaCustomer.tagged_with(preferred_brands, :on => :preferred_brands, any: true).to_sql
This is really slow (~12 secs) has we have more than a million of customers. I'm not really familiar with
EXISTS
but runningEXPLAIN
showcase that no index is then used on the MetaCustomerwhere
queryI did a small tweak so the subquery return
ids
instead to avoid using exist and rely on the primary key index. The result run in 78ms for the same result.Is anyone having the same issue? Am I missing an index ?