[eluser]Unknown[/eluser]
Hi,
I have created the index for the below table, but its not working correctly for some queries, I don't know where I have done mistake, please help me.
For the query, "explain select * from videos_ctgryDomain where domain = 1;", its scanning all the rows in the table, but for other domains, its coming correctly.
mysql> desc videos_ctgryDomain;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| domain | int(11) | NO | MUL | NULL | |
| ctgry | int(11) | NO | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from videos_ctgryDomain;
+--------+-------+
| domain | ctgry |
+--------+-------+
| 9 | 9 |
| 9 | 5 |
| 3 | 14 |
| 3 | 12 |
| 1 | 4 |
| 1 | 2 |
| 1 | 14 |
| 1 | 12 |
+--------+-------+
8 rows in set (0.00 sec)
mysql> show index from videos_ctgryDomain;
+--------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| videos_ctgryDomain | 1 | indx_domain | 1 | domain | A | 8 | NULL | NULL | | BTREE | | |
+--------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> explain select * from videos_ctgryDomain where domain = 1;
+----+-------------+--------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | videos_ctgryDomain | ALL | indx_domain | NULL | NULL | NULL | 8 | Using where |
+----+-------------+--------------------+------+---------------+------+---------+------+------+-------------+
mysql> explain select * from videos_ctgryDomain where domain = 3;
+----+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | videos_ctgryDomain | ref | indx_domain | indx_domain | 4 | const | 2 | NULL |
+----+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
mysql> explain select * from videos_ctgryDomain where domain = 9;
+----+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | videos_ctgryDomain | ref | indx_domain | indx_domain | 4 | const | 2 | NULL |
+----+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
Thanks in advance.
