Help combining two MySQL queries to improve performance

Question

I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query:

SELECT d.field_id_105 AS code,
       title
FROM exp_channel_titles AS t
INNER JOIN exp_channel_data AS d ON (d.entry_id = t.entry_id)
WHERE t.channel_id = 18
AND t.status = 'open'
AND t.author_id = 123
ORDER BY t.entry_id ASC

Then, using a PHP foreach loop, I'm looping through each of those codes and performing a second query on each one, which is obviously incredibly inefficient.

The second query varies slightly depending on whether the code comprises one or two letters. If it comprises 2 letters, I simply want to find all records where a specific field starts with those two letters. If it comprises one letter, I want to find all records where a specific field starts with that letter followed by any number from 1 to 9 (e.g. G1 or W8).

Here are the two different versions of the second query:

If the code comprises two letters

SELECT t.title AS title,
       t.entry_date AS entry_date,
       t.entry_id AS entry_id,
       i.title AS installer,
       i.entry_id AS installer_id,
       d.entry_id
FROM exp_channel_data AS d
INNER JOIN exp_relationships AS r ON (r.parent_id = d.entry_id AND r.field_id = 48)
INNER JOIN exp_channel_titles AS t ON (d.entry_id = t.entry_id AND t.status = 'open')
INNER JOIN exp_channel_titles AS i ON (r.child_id = i.entry_id AND i.status = 'open')
WHERE LEFT(field_id_56, 2)  = 'AB'
AND d.channel_id = 12

If the code comprises one letter:

SELECT t.title AS title,
       t.entry_date AS entry_date,
       t.entry_id AS entry_id,
       i.title AS installer,
       i.entry_id AS installer_id,
       d.entry_id
FROM exp_channel_data AS d
INNER JOIN exp_relationships AS r ON (r.parent_id = d.entry_id AND r.field_id = 48)
INNER JOIN exp_channel_titles AS t ON (d.entry_id = t.entry_id AND t.status = 'open')
INNER JOIN exp_channel_titles AS i ON (r.child_id = i.entry_id AND i.status = 'open')
WHERE LEFT(field_id_56, 2) IN ('G1','G2','G3','G4','G5','G6','G7','G8','G9')
AND d.channel_id = 12

I've 'hard-coded' the values in the WHERE clauses for demo purposes, but they would actually be populated dynamically by PHP.

So essentially what I'd like to do is combine all of these queries into one query. I'm not sure if that's even possible. I got as far as using the first query as a subquery inside the LEFT() function, but I'm getting a little out of my depth at that point and can't work out how to handle appending the numbers to the single-letter codes. Maybe some REGEXP, but I'm not sure.

Any help appreciated, thanks.

Answer 1

I'm assuming that G followed by anything is valid in the second case. If so you sould be able to replace your 3 queries with something like:

SELECT d.field_id_105 AS code
     , t.title AS title,
     , t.entry_date AS entry_date
     , t.entry_id AS entry_id
     , i.title AS installer
     , i.entry_id AS installer_id
     , d.entry_id
FROM exp_channel_data AS d
JOIN exp_relationships AS r 
    ON r.parent_id = d.entry_id 
   AND r.field_id = 48
JOIN exp_channel_titles AS t 
    ON d.entry_id = t.entry_id 
   AND t.status = 'open'
JOIN exp_channel_titles AS i 
    ON r.child_id = i.entry_id 
   AND i.status = 'open'
WHERE SUBSTR(d.field_id_56,1
            ,CASE WHEN SUBSTR(d.field_id_56,1,2) REGEXP '[A-Z][0-9]'
                                    THEN 1 
                                    ELSE 2
                               END)
    = SUBSTR(d.field_id_105,1
            ,CASE WHEN SUBSTR(d.field_id_56,1,2) REGEXP '[A-Z][0-9]' 
                                    THEN 1 
                                    ELSE 2
                               END)
AND d.channel_id = 12;

This is untested and may contain syntax errors since there where no ddl in the question.

Answer 2

Depending on the distribution of data, it may be faster to run 3 queries and combine the results:

( SELECT ... WHERE field_id_56 LIKE 'AB%' ... )
UNION ALL
( SELECT ... WHERE field_id_56 LIKE 'G%' ... )
UNION ALL
( SELECT ... WHERE field_id_56 LIKE 'WO%' ... )

and have

INDEX(channel_id, field_id_56) -- in that order

(I am assuming that those two columns are in the same table. Please provide aliases on every column when joining!)