does-mariadb-do-nested-where-optimization.sql.

SELECT
        p.name,
        c.id as cliente_id,
        coalesce(cp.value, p.initial_value) as value
    FROM preferences p,
      clientes c
      LEFT JOIN clientes_preferences cp ON cp.cliente_id = c.id AND cp.preference_id = c.id
where c.id=180 and p.name='NOTIFY_COMMENTS_POSTS_CREATED';

/*
+------+-------------+-------+-------+--------------------------+------------+---------+-------+------+-------------------------------------------------+
| id   | select_type | table | type  | possible_keys            | key        | key_len | ref   | rows | Extra                                           |
+------+-------------+-------+-------+--------------------------+------------+---------+-------+------+-------------------------------------------------+
|    1 | SIMPLE      | c     | const | PRIMARY                  | PRIMARY    | 4       | const | 1    | Using index                                     |
|    1 | SIMPLE      | cp    | ref   | cliente_id,preference_id | cliente_id | 4       | const | 1    | Using where                                     |
|    1 | SIMPLE      | p     | ALL   | NULL                     | NULL       | NULL    | NULL  | 5    | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+--------------------------+------------+---------+-------+------+-------------------------------------------------+
*/
 
 -- versus


select * from (
   SELECT
        p.name,
        c.id as cliente_id,
        coalesce(cp.value, p.initial_value) as value
    FROM preferences p,
      clientes c
      LEFT JOIN clientes_preferences cp ON cp.cliente_id = c.id AND cp.preference_id = c.id
) x
where x.cliente_id = 180 and x.name = 'NOTIFY_COMMENTS_POSTS_CREATED';
/*
+------+-------------+-------+-------+--------------------------+------------+---------+-------+------+------------------------------------+
| id   | select_type | table | type  | possible_keys            | key        | key_len | ref   | rows | Extra                              |
+------+-------------+-------+-------+--------------------------+------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | c     | const | PRIMARY                  | PRIMARY    | 4       | const | 1    | Using index                        |
|    1 | SIMPLE      | cp    | ref   | cliente_id,preference_id | cliente_id | 4       | const | 1    | Using where                        |
|    1 | SIMPLE      | p     | ALL   | NULL                     | NULL       | NULL    | NULL  | 5    | Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+--------------------------+------------+---------+-------+------+------------------------------------+
*/