1

I have the following query that I'm using and was wondering if it would work performantly, or whether I should use ElasticSearch from the start:

SELECT 
  * 
FROM 
  entity_access 
  JOIN entity ON (entity.id=entity_access.entity_id)
WHERE
  user_id = 144
  AND name LIKE '%format%'

The entity_access table will have about a billion results. But each user should have 5k entries max. My thinking was that a LIKE %term% would be trivial on a table of 5k rows (under 50ms), so hopefully it would be the same if I have a good index on a large table before doing it? Or is there something I'm missing here?

  • `LIKE '%format%'` cannot use an index. If you really have a need to assert this condition quickly, then consider creating a materialized view. – Tim Biegeleisen Nov 16 '18 at 03:00
  • 1
    Qualify your column names. Without knowing where the columns come from, nothing can really be usefully said about performance. – Gordon Linoff Nov 16 '18 at 03:58

1 Answers1

1

Two things. First, it doesn't matter how many total rows in the table, because the index on user_id will select only those rows for matching. As you say there are about 5k per user_id, then that's easily managed.

Second, LIKE '%foo%' will not use an index: the leading '%' precludes that. If you want to use an index, you'll have to accept a pattern of LIKE 'foo%'. If that fits the use case, then the query as written will perform fine.

If either of the above conditions doesn't hold, then consider using a dedicated search engine (like Sphinx, or roll-your own with radix trees) or materialize your search into a more indexable format (such as using MySQL Full-Text Search).

bishop
  • 37,830
  • 11
  • 104
  • 139