does-mariadb-have-where-pushdown-otimization.sql

SELECT
        p.name,
        c.id as cliente_id,
        coalesce(cp.value, p.initial_value) as value
    FROM preferences p
    CROSS JOIN clientes c
    LEFT JOIN clientes_preferences cp ON cp.cliente_id = c.id AND cp.preference_id = p.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      | p     | ALL        | NULL                     | NULL                     | NULL    | NULL          | 5      | Using where                     |
|    1 | SIMPLE      | cp    | ref|filter | cliente_id,preference_id | preference_id|cliente_id | 4|4     | directus.p.id | 4 (6%) | Using where; Using rowid filter |
+------+-------------+-------+------------+--------------------------+--------------------------+---------+---------------+--------+---------------------------------+
3 rows in set (0.003 sec)

*/
 
 -- versus


create view view_user_preferences as
   SELECT
        p.name,
        c.id as cliente_id,
        coalesce(cp.value, p.initial_value) as value
    FROM preferences p
    CROSS JOIN clientes c
    LEFT JOIN clientes_preferences cp ON cp.cliente_id = c.id AND cp.preference_id = p.id
;


explain select * from view_user_preferences where cliente_id=180 and 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      | p     | ALL        | NULL                     | NULL                     | NULL    | NULL          | 5      | Using where                     |
|    1 | SIMPLE      | cp    | ref|filter | cliente_id,preference_id | preference_id|cliente_id | 4|4     | directus.p.id | 4 (6%) | Using where; Using rowid filter |
+------+-------------+-------+------------+--------------------------+--------------------------+---------+---------------+--------+---------------------------------+
*/

versus

explain select * from (
    ->     SELECT
    ->         p.name,
    ->         c.id as cliente_id,
    ->         coalesce(cp.value, p.initial_value) as value
    ->     FROM preferences p
    ->     CROSS JOIN clientes c
    ->     LEFT JOIN clientes_preferences cp ON cp.cliente_id = c.id AND cp.preference_id = p.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      | p     | ALL        | NULL                     | NULL                     | NULL    | NULL          | 5      | Using where                     |
|    1 | SIMPLE      | cp    | ref|filter | cliente_id,preference_id | preference_id|cliente_id | 4|4     | directus.p.id | 4 (6%) | Using where; Using rowid filter |
+------+-------------+-------+------------+--------------------------+--------------------------+---------+---------------+--------+---------------------------------+

*/