Skip navigation links

MySQL Forums :: PHP :: Indexing is done for single table, but its not working for most of the queries.


Advanced Search

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. shankar ganesh 06/13/2013 01:05AM
Re: Indexing is done for single table, but its not working for most of the queries. Rick James 06/14/2013 11:31PM


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.