• 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
    WHERE EXISTS 
            (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.

Your innodb_buffer_pool_size is enormous. You have it set at 20971520000. That's 19.5135 GB. If you only have 5GB of InnoDB data and indexes, then you should only have about 8GB. Even this may be too high.

Here is what you should do. First run this query

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

This will give you the RIBPS, Recommended InnoDB Buffer Pool Size, based on all InnoDB Data and Indexes, with an additional 60%.

For Example

mysql>     SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    ->     (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    ->     FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
|     8 |
+-------+
1 row in set (4.31 sec)

mysql>

With this output, you would set the following in /etc/my.cnf

[mysqld]
innodb_buffer_pool_size=8G

Next, service mysql restart

After the restart, run MySQL for a week or two. Then, run this query:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

This will give you how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment.

I have written about this before : What to set innodb_buffer_pool and why..?

You could just run this DataGB query right now rather than reconfiguring, restarting and waiting a week.

This value DataGB more closely resembles how big the InnoDB Buffer Pool should be + (percentage specified in innodb_change_buffer_max_size). I am sure this will be far less than the 20000M you have reserved right now. The savings in RAM can be used for tuning other things like

• join_buffer_size
• sort_buffer_size
• read_buffer_size
• read_rnd_buffer_size
• max_connection

CAVEAT #1

This is very important to note: At times, InnoDB may require an additional 10% over the value for the innodb_buffer_pool_size. Here is what the MySQL Documentation says on this:

The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:

Competition for physical memory might cause paging in the operating system.

InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.

The address space must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.

The time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time may be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 8.9.1, “The InnoDB Buffer Pool”.

CAVEAT #2

I See the following values in your my.cnf

| innodb_io_capacity                                | 200 |
| innodb_read_io_threads                            | 4   |
| innodb_thread_concurrency                         | 4   |
| innodb_write_io_threads                           | 4   |

These number will impede InnoDB from accessing multiple cores

Please set the following:

[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

I have written about this before in the DBA StackExchange

• May 26, 2011: About single threaded versus multithreaded databases performance
• Sep 12, 2011: Possible to make MySQL use more than one core?
• Sep 20, 2011: Multi cores and MySQL Performance

I just answered a question like this in Server Fault using a more concise formula:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1.25 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;