MariaDB [test]> select count(*) from alf_node;

+----------+

| count(*) |

+----------+

|   321777 |

+----------+

1 row in set (0.060 sec)

MariaDB [test]> select count(*) from alf_node_aspects;

+----------+

| count(*) |

+----------+

|  8192000 |

+----------+

1 row in set (17.464 sec)

MariaDB [test]> select count(*) from alf_node_properties;

+----------+

| count(*) |

+----------+

|   216000 |

+----------+

1 row in set (0.659 sec)

MariaDB [test]> select node.id as id from alf_node node left outer join alf_node_properties PROP_0 on (PROP_0.node_id = node.id) AND (43 = PROP_0.qname_id) where node.type_qname_id <> 149 AND node.store_id = 6 AND node.type_qname_id IN (35) AND node.id IN (select PROP.node_id from alf_node_properties PROP where 919 = PROP.qname_id AND PROP.string_value = 'PKKM' AND node.id IN (select aspect.node_id from alf_node_aspects aspect where aspect.qname_id IN (1146) AND node.id IN (select PROP.node_id from alf_node_properties PROP where (1144 = PROP.qname_id) AND PROP.boolean_value = 1) order by PROP_0.string_value ASC));

Empty set (0.001 sec)

Explain Plan :

------------

MariaDB [test]> explain select node.id as id from alf_node node left outer join alf_node_properties PROP_0 on (PROP_0.node_id = node.id) AND (43 = PROP_0.qname_id) where node.type_qname_id <> 149 AND node.store_id = 6 AND node.type_qname_id IN (35) AND node.id IN (select PROP.node_id from alf_node_properties PROP where 919 = PROP.qname_id AND PROP.string_value = 'PKKM' AND node.id IN (select aspect.node_id from alf_node_aspects aspect where aspect.qname_id IN (1146) AND node.id IN (select PROP.node_id from alf_node_properties PROP where (1144 = PROP.qname_id) AND PROP.boolean_value = 1) order by PROP_0.string_value ASC))\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: aspect

         type: ref

possible_keys: fk_alf_nasp_n,fk_alf_nasp_qn

          key: fk_alf_nasp_qn

      key_len: 8

          ref: const

         rows: 1

        Extra: Start temporary

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: node

         type: eq_ref

possible_keys: PRIMARY,fk_alf_node_store,idx_alf_node_mdq

          key: PRIMARY

      key_len: 8

          ref: test.aspect.node_id

         rows: 1

        Extra: Using where

*************************** 3. row ***************************

           id: 1

  select_type: PRIMARY

        table: PROP

         type: ref

possible_keys: fk_alf_nprop_n,fk_alf_nprop_qn,idx_alf_nprop_s,idx_alf_nprop_l,idx_alf_nprop_b,idx_alf_nprop_f,idx_alf_nprop_d

          key: idx_alf_nprop_s

      key_len: 137

          ref: const,const

         rows: 1

        Extra: Using where

*************************** 4. row ***************************

           id: 1

  select_type: PRIMARY

        table: PROP

         type: ref

possible_keys: fk_alf_nprop_n,fk_alf_nprop_qn,idx_alf_nprop_s,idx_alf_nprop_l,idx_alf_nprop_b,idx_alf_nprop_f,idx_alf_nprop_d

          key: fk_alf_nprop_qn

      key_len: 8

          ref: const

         rows: 1

        Extra: Using where

*************************** 5. row ***************************

           id: 1

  select_type: PRIMARY

        table: PROP_0

         type: ref

possible_keys: fk_alf_nprop_n,fk_alf_nprop_qn,idx_alf_nprop_s,idx_alf_nprop_l,idx_alf_nprop_b,idx_alf_nprop_f,idx_alf_nprop_d

          key: fk_alf_nprop_n

      key_len: 8

          ref: test.aspect.node_id

         rows: 252

        Extra: Using where; End temporary

5 rows in set (0.001 sec)

node1:root@localhost> select count(1) from alf_node_properties;   

+-----------+

| count(1)  |

+-----------+

| 206600536 |

+-----------+

1 row in set (13 min 30.436 sec)

node1:root@localhost> 

node1:root@localhost> select count(1) from alf_node;

+-----------+

| count(1)  |

+-----------+

| 105317344 |

+-----------+

1 row in set (4 min 31.001 sec)

node1:root@localhost> select count(1) from alf_node_aspects;

+-----------+

| count(1)  |

+-----------+

| 180000451 |

+-----------+

1 row in set (7 min 32.753 sec)

node1:root@localhost> 

node1:root@localhost> select node.id as id from alf_node node left outer join alf_node_properties PROP_0 on (PROP_0.node_id = node.id) AND (43 = PROP_0.qname_id) where node.type_qname_id <> 149 AND node.store_id = 6 AND node.type_qname_id IN (35) AND node.id IN (select PROP.node_id from alf_node_properties PROP where 919 = PROP.qname_id AND PROP.string_value = 'PKKM' AND node.id IN (select aspect.node_id from alf_node_aspects aspect where aspect.qname_id IN (1146) AND node.id IN (select PROP.node_id from alf_node_properties PROP where (1144 = PROP.qname_id) AND PROP.boolean_value = 1) order by PROP_0.string_value ASC));

+----+

| id |

+----+

| 32 |

+----+

1 row in set (0.002 sec)

node1:root@localhost>