Avoiding a problematic nested query in MySQL

Question

I have this SQL query which due to my own lack of knowledge and problem with mysql handling nested queries, is really slow to process. The query is...

SELECT    DISTINCT PrintJobs.UserName
FROM      PrintJobs
LEFT JOIN Printers
ON        PrintJobs.PrinterName = Printers.PrinterName
WHERE     Printers.PrinterGroup
IN        (
              SELECT    DISTINCT Printers.PrinterGroup
              FROM      PrintJobs
              LEFT JOIN Printers
              ON        PrintJobs.PrinterName = Printers.PrinterName
              WHERE     PrintJobs.UserName='<username/>'
          );

I would like to avoid splitting this into two queries and inserting the values of the subquery into the main query progamatically.

Answer 1

This is probably not exactly what you are looking for however, i will contribute my 2 cents. First off you should show us your schema and exactly what you are trying to accomplish with that query. However from the looks of it you are not using numeric IDs in the table and are instead using varchar fields to join tables, this is not really a good idea performance wise. Also i am not sure why you are doing:

(select PrinterName, UserName
      from PrintJobs) AS Table1

instead of just joining on PrintJobs? Similar stuff for this one:

(select
      PrinterName,
      PrinterGroup
      from Printers) as Table1

Maybe i am just not seeing it right. I would recommend that you simplify the query as much as possible and try it. Also tell us what exactly you are hoping to accomplish with the query and give us some schema to work with.

Removed the bad query from the answer.

Answer 2

This query you have is pretty messed up, not sure if this will handle everything you need but simplifying like this kills all the nested queries and it way faster. You can also use the EXPLAIN command to know how mysql will fetch your query.

SELECT    DISTINCT PrintJobs.UserName
FROM      PrintJobs

LEFT JOIN Printers ON PrintJobs.PrinterName = Printers.PrinterName
AND Printers.Username = '<username/>'
;