I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table.

  1. I want records that match ((criterion 1 or criterion 2) and criterion 3).

  2. I want the criteria statements to add columns to the results.

  3. I need a standard way to assemble the queries because I'm making a 'search builder'; so I can't optimise every combination. It can be assumed that each criterion will return zero or one row.

EG1: I first tried

SELECT people.*
  FROM people
       LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
       (criterion3 SELECT) c3
 WHERE people.id=c3.pid
   AND ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );

This was far too slow - it never actually returned records in the minute I allowed it!

EG2:Next I tried

SELECT people.*
  FROM people,
       (       (criterion1 SELECT) 
         UNION (criterion2 SELECT) ) c12
 WHERE people.id=c12.pid
   AND people.id IN (criterion3 SELECT)

This one returns 2000+ rows in 0.6s, which is acceptable. But you can't add any columns from criterion 3, and can't access columns from c1 and c2 either, really, since one will mask another.

EG3:Out of interest, I moved the c12 into the WHERE clause:

SELECT people.*
  FROM people
 WHERE id IN (criterion3 SELECT)
   AND id IN ( (criterion1 SELECT) 
               UNION (criterion2 SELECT) )

but again, this threw it for well over a minute before I killed the query.

EG4: So I returned to the EG2 code, and did this ugly thing:

  FROM ( all the EG2 SQL ) src
       LEFT JOIN (criterion1 SELECT) c1 ON src.id=c1.pid
       LEFT JOIN (criterion2 SELECT) c2 ON src.id=c2.pid

So the c1 and c2 queries are added in twice! This returns the (same number of) results, with the extra fields, in 6s. Too slow, really, but at least it runs. And really ugly!

Can anyone give me any pointers to explain any of this? Why is EG3 massively faster than EG1/EG4 when they're all doing the same thing? Why should running the queries twice (EG4) be faster than running them once (EG1)?!

EDIT: Using MySQL 5.1, InnoDB tables.

  • Can you provide actual code instead of (criterion1 SELECT)? And tables' definitions and indexes? – ypercubeᵀᴹ May 31 '12 at 9:56
  • Thank you for your interest. There are about 15 tables and about 30 different combination of query, so no, this is not realistic. The queries won't always be optimised because they need to be flexible and/or I can't afford zillions of indexes on every table. My question is about the theory, really. Perhaps it's unanswerable. – artfulrobot May 31 '12 at 10:32
  • No, it's still a good question in my opinion. – ypercubeᵀᴹ May 31 '12 at 10:36
  • There's always the vote-up button for questions you think are useful...! Might attract some others to answer it. :-) – artfulrobot May 31 '12 at 10:57
  • Do you use MyISAM or InnoDB engine? – ypercubeᵀᴹ May 31 '12 at 11:16
  • Why there are massive differences in performance between the queries?
    (assuming that you have at least defined indexes for the columns used in the subquery criteria and in the joining conditions)

Basically because MySQL optimizer is not smart enough to figure out that all these queries are equivalent. So, it probably produces different execution plans for the different queries. If you do not have properly defined FOREIGN KEY constraints, the optimizer may be actually right, there is no guarantee that the queries return identical results.

  • So, what to do to increase performance and have a standard way of writing this type of query variants?

Several things that affect MySQL queries performance:

  1. Do not use id IN (SELECT subquery) if you can avoid it. It's not very well optimized in most MySQL versions (see point 6 below). Use joins if you can.

  2. Replace UNION with UNION ALL if that doesn't change the result set (you could do that in EG3 query).

  3. Don't use implicit joins (with commas in the FROM clause and the joining condition in the WHERE clause). Use explicit JOIN syntax. As an example, your EG1 is actually the same as (this is not for performance but for consistence):

    SELECT people.*
      FROM people
           LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid
           LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid,
           JOIN (criterion3 SELECT) c3 ON people.id=c3.pid
     WHERE ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );
  4. The above query has an OR condition that is relevant to 2 tables (and their joins to the people, so 3 tables actually). This is usually not very good perfomance-wise.

  5. You can try rewriting using EXISTS. This will make your queries easier to write and it may help with performance, too:

    SELECT people.*
      FROM people
             (criterion3 SELECT modified with `people.id = some_table.pid`)
       AND ( EXISTS 
               (criterion1 SELECT modified with `people.id = some_table.pid`)
          OR EXISTS 
               (criterion2 SELECT modified with `people.id = some_table.pid`) 
           ) ;
  6. If your host allows it, try/test MariaDB (it's a MySQL fork-replacement) that has introduced several improvements in queries execution in its latest versions. The optimizer will be a bit smarter identifying equivalent queries and certainly smarter as it has some new algorithms implemented that affect queries will joins, subqueries among other things.

MySQL 5.6 has also a few improvements in the optimizer but it's not available yet as a stable release.

| improve this answer | |
  • Thank you for your time and expertise. I will have a look at MariaDB because it's my server so I can do what I want (oh, yeah, as long as I don't break everything). At least in my test queries IN () performs the same as EXISTS (), but happy to go with your general advice. Any idea why adding in the data as columns adds such an overhead (10x query time)? Even adding LEFT JOINs that are unused in the SELECT adds this overhead. – artfulrobot May 31 '12 at 11:28
  • If there are such big differences, I suspect you may have some index missing and the execution plan includes full scanning of one or more tables. You can provide a sample query (edit and add in the end of your question). Or perhaps add another question. – ypercubeᵀᴹ May 31 '12 at 11:36
  • Thank you for offering to help. I'll take a look at that myself. I think there's something I don't understand in that if it can get to the data efficiently as a where clause, why can't it get to the same data efficiently for a join or select. – artfulrobot May 31 '12 at 12:25
  • 1
    @artfulrobot: You can setup a few tables and the queries at SQL-Fiddle and then share the link so we can have a look. Add a comment here or join us at The-Heap. There's almost always someone there that can help. – ypercubeᵀᴹ May 31 '12 at 12:29
  • 1
    Cool tool. SQL-fiddle without joined data and then the one that is 10x slower: SQL-fiddle with joined data (well actually, there isn't any joined data other than the pid column which we already know, but it still slows it down 10x). I've not put in any data. Hmmm. Obvious limitation, but the dataset is vast, so obviously can't do that. – artfulrobot May 31 '12 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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