To route requests on my website, I need to query the database to see if the first part of a URL matches a category for an item.

For example:

I have a request to /animals/dog/darktheme/. The category is 'animals', thus the category_url in the mysql DB is /animals/.

SELECT DISTINCT category_url FROM items
    WHERE '/animals/dog/darktheme/' LIKE CONCAT(category_url,'%');


This query will have to run for literally every page load of my site. I'm still in the planning stage, thus can change my design if need be.

Does this kind of query perform reasonably well? Let's say there's 10,000 items and 250 different category_urls which are all like /animals/, /birds/, /reptiles/, etc.

Any tips on optimizing performance for this kind of query, or any better ways to do it, from the database/query perspective?

  • I use PHP with the PDO class/extension and I always use paramaterized queries provided by PDO to prevent sql injection. In case anyone worried.
    – Reed
    Jun 21 '19 at 17:08
  • 1
    How about WHERE category_url = '/animals' OR category_url = '/animals/dog' OR category_url = '/animals/dog/darktheme' so an index can be used? Jun 21 '19 at 17:13
  • Might that be faster than the CONCAT option? What about WHERE category_url IN ('/animals/', '/animals/dog/', '/animals/dog/darktheme/'? I can see this getting "hairy" if my category_url is something like /animals/big/furry/, thus the OR/IN statement would get very long. The point about an index being used makes me think, probably faster than the concat option.
    – Reed
    Jun 21 '19 at 17:16
  • Does CONCAT use an index? I'm a PHP developer so I have only... as-needed experience with mysql.
    – Reed
    Jun 21 '19 at 17:19
  • 1
    The problem is not CONCAT but that you apply it to a column. Conditions like WHERE column = @something or WHERE column IN (@some_list) can use indexes. But WHERE function(column) = @something no. Jun 21 '19 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.