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.

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!