MySQL Forums
Forum List  »  PHP

Indexing is done for single table, but its not working for most of the queries.
Posted by: shankar ganesh
Date: June 13, 2013 01:05AM

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.



Edited 1 time(s). Last edit at 06/13/2013 01:07AM by shankar ganesh.

Options: ReplyQuote


Subject
Written By
Posted
Indexing is done for single table, but its not working for most of the queries.
June 13, 2013 01:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.