For anyone struggling with optimizing search through an EAV table, this post may be able to help.
The way an EAV model is structured doesn’t provide MySQL with the fastest way to search through those tables. Depending on data you are putting in there you could have hundreds of rows all linked to one user. Doing a like on those makes SQL do a like PER row, which drastically affects performance. I found a little trick which can make this process a whole lot faster.
GROUP_CONCAT(`table_data`.`value`) AS `table_data_search`
Doing a group_concat selects all the data and concats it together in 1 big long string which will allow us to only have to do a comparison on 1 field. This increased performance of the query by over 300%
GROUP BY
`model`.id
HAVING `model_data_search` LIKE ‘%query%’
After you group by, using HAVING will allow you to do comparison to the group_concat (WHERE clause is executed before GROUP_CONCAT so you cant use those values in the WHERE clause)
I hope this helps someone out there looking for it.
Additional References:
Thanks for reading,
- Michael Kramer