1

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.

4
  • If I get it right you check if d.field_id_105 contains 1 or 2 letters, and then fire of query 2 or 3. What I don't understand is how the result from q1 relates to to q2. Say that q1 returnes 3 rows, then one of q2 or q3 is executed more than once with the same parameters? Jun 9, 2016 at 17:22
  • @Lennart Yeah, so say q1 returns three rows: AB, WO and G. I would then run q2 once for "AB" and once for "WO". And I would run q3 once for IN ('G1', ... 'G9'). I have tried using some PHP to create an array of all values and putting them in a single IN clause, e.g. IN ('AB','WO','G1','G2',...'G'9) and doing away with q2 altogether, but that didn't improve performance.
    – Pete H
    Jun 10, 2016 at 8:11
  • The first time you run q2 with the predicate: LEFT(field_id_56, 2) = 'AB' and in the second time: LEFT(field_id_56, 2) = 'WO', is that correct? Another question, if first character in field_id_56 is 'G' can the second character be something other than '0' - '9'? Jun 10, 2016 at 10:17
  • @Lennart that's correct. I suppose I hadn't really thought about your second question. The second character in field_id_56 could be a number or a letter. They're actually UK post codes, some of which start with two letters (e.g. "EC1"), and some of which start with a single letter followed by a number (e.g. W3). In case it wasn't clear, "G" is just an example. That first letter could be any letter.
    – Pete H
    Jun 10, 2016 at 10:45

2 Answers 2

0

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.

1
  • Thanks for this, however it doesn't seem quite right. It looks like you've lost the t.channel_id = 18 and t.author_id = 123 from the first query.
    – Pete H
    Jun 10, 2016 at 14:16
0

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!)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.