April 10, 2012
Is there a performance friendly way to list results for each letter in the database?
Question by Gerben Jacobs
I want to create an index page with a couple of results for each letter of the alphabet.
At the moment I have this:
SELECT url_slug, name FROM artists WHERE name LIKE "A%" ORDER BY rand() LIMIT 10
SELECT url_slug, name FROM artists WHERE name LIKE "B%" ORDER BY rand() LIMIT 10
SELECT url_slug, name FROM artists WHERE name LIKE "C%" ORDER BY rand() LIMIT 10
SELECT url_slug, name FROM artists WHERE name LIKE "D%" ORDER BY rand() LIMIT 10
SELECT url_slug, name FROM artists WHERE name LIKE "E%" ORDER BY rand() LIMIT 10
SELECT url_slug, name FROM artists WHERE name LIKE "F%" ORDER BY rand() LIMIT 10
Is there another, a more performance friendly, way to achieve this?
P.s. I know ‘order by rand()’ isn’t very good. But this is just in development.
Answer by Starx
For the purpose you are trying to get, the query does not get better than that IMO. However, if the table is enormous, if might want to make occasional cache table holding the values of a fixed letter separately.
And indexing, index you records, as per the fields.
