I have big enough dataset and few joins to get them all
with help next query:
SELECT DISTINCT c.c_id FROM c_active z1 INNER JOIN cs c ON (z1.cv_id=c.cv_id) INNER JOIN indi i ON (c.m_id=i.m_id) INNER JOIN c_loc cl ON (z1.c_id=c.c_id) INNER JOIN profs cp ON (z1.c_id=cp.c_id) WHERE i.sex='2' AND c.lang='en' AND cl.is_country='0' AND cl.location_id IN (3,4,5,6) AND (cp.cat_id IN ('13', '2', '20'))
and this is execution plan which is provided by mysql 5.6
+----+-------------+-------+--------+------------------------+---------+---------+--------+----------+---------------------------------------------------------------------------+
| id | select_type | table | type | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+------------------------+---------+---------+--------+----------+---------------------------------------------------------------------------+
| 1 | SIMPLE | i | ref | sex | 1 | const | 306937 | 100.00 | Using index; Using temporary |
| 1 | SIMPLE | c | ref | m_id | 4 | i.m_id | 1 | 100.00 | Using where |
| 1 | SIMPLE | z1 | eq_ref | PRIMARY | 4 | c.c_id | 1 | 100.00 | Using index; Distinct |
| 1 | SIMPLE | cp | ref | c_id | 4 | c.c_id | 1 | 100.00 | Using where; Distinct |
| 1 | SIMPLE | cl | range | is_country_location_id | 4 | NULL | 936608 | 100.00 | Using where; Using index; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+------------------------+---------+---------+--------+----------+---------------------------------------------------------------------------+
Is there any way how to make that query faster ? I see that in last line number of rows is huge, and this is the reason why it is slow.
I see that cardinality for cl.c_id table
is huge and for cl.is_country_location_id
is low
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| c_loc | 0 | PRIMARY | 1 | id | A | 1211146 | NULL | NULL | | BTREE | | |
| c_loc | 1 | cv_id | 1 | c_id | A | 1211146 | NULL | NULL | | BTREE | | |
| c_loc | 1 | is_country_location_id | 1 | is_country | A | 2 | NULL | NULL | | BTREE | | |
| c_loc | 1 | is_country_location_id | 2 | location_id | A | 574 | NULL | NULL | | BTREE | | |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
and from that point maybe this is why mysql going to go through so many records.
But what could be the strategy to optimize such query ?