Mysql – Optimizing a query that retrieves five rows for each letter of the alphabet

greatest-n-per-groupMySQL

A site that I'm currently working on will execute a query 26 times but only changes a letter within the where clause. I am looking to make this more efficient by running only one query.

Here is an example query:

SELECT table1.id, table1.name, table1.url
FROM table1
       LEFT JOIN table2 ON table1.aid = table2.aid
WHERE LOWER(table1.name) LIKE 'a%'
  AND table1.rank > 0
  AND table1.view = 1
ORDER BY table2.important DESC, table1.rank ASC
LIMIT 5

I have managed to create a temporary table in which would return each letter of the alphabet however I cannot figure out how to fetch the letter and push it into the where clause.

I will end up creating a table for the alphabet but this is how it currently runs (for just now):

SELECT *
FROM (SELECT 'a' AS letter
    UNION SELECT 'b'
    UNION SELECT 'c'
    ...
    UNION SELECT 'y'
    UNION SELECT 'z') AS alphabet

Clarification: I didn't really explain what I am looking to do. I am looking to pull 5 rows for each letter.

Any and all help would be greatly appreciated.

Best Answer

You have to use the REGEX_LIKE or briefly RLIKE function:

. . . . . .
WHERE RLIKE(table1.name, '^[:alpha:].*', 'i')
. . . . . .

Here ^ mean "from the very beginning of the string",
[:alpha:] is any alphabetical character
.* is any number of any characters and
'i' is a flag that mean case-insensitive comparison.

RLIKE() function returns TRUE if matched and FALSE otherwise.

Further reading can be found here: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

UPDATE 

SELECT table1.id
     , table1.name
     , table1.url
     , IF(@tname <> @tname := table1.name, @cnt := 1, @cnt := @cnt + 1) AS cnt
  FROM table1
  LEFT JOIN table2 ON table1.aid = table2.aid
 WHERE RLIKE(table1.name, '^[:alpha:].*', 'i')
   AND table1.rank > 0
   AND table1.view = 1
 ORDER BY table1.name ASC
        , table2.important DESC
        , table1.rank ASC
HAVING cnt <= 5
Related Question