mysql index decision
Moderator: General Moderators
mysql index decision
Hi,
here is a sample table:
picture
{
int id;
int enabled; // takes two values: '1' or '0'
char name[20];
INDEX(enabled,name);
}
so i have an index on 2 columns.
here is a query selecting rows with name 'Julie':
"SELECT id FROM picture WHERE name='Julie'"
the question is, is mysql smart enough to figure out that it should use the existing index?
i mean, something similar to:
"SELECT id FROM picture WHERE enabled=1 AND name='Julie' UNION SELECT id FROM picture WHERE enabled=0 AND name='Julie' "
if not, will FORCE INDEX directive help?
thanks
here is a sample table:
picture
{
int id;
int enabled; // takes two values: '1' or '0'
char name[20];
INDEX(enabled,name);
}
so i have an index on 2 columns.
here is a query selecting rows with name 'Julie':
"SELECT id FROM picture WHERE name='Julie'"
the question is, is mysql smart enough to figure out that it should use the existing index?
i mean, something similar to:
"SELECT id FROM picture WHERE enabled=1 AND name='Julie' UNION SELECT id FROM picture WHERE enabled=0 AND name='Julie' "
if not, will FORCE INDEX directive help?
thanks
Code: Select all
CREATE TABLE `picture` (
`id` INT NOT NULL AUTO_INCREMENT ,
`enabled` TINYINT NOT NULL ,
`name` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `id` )
)Code: Select all
ALTER TABLE `picture` ADD INDEX ( `enabled` , `name` )- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Hmm, not entirely sure how MySQL will work with it, but you can find out:run that in phpMyAdmin
Code: Select all
EXPLAIN SELECT `id` FROM `picture` WHERE `name` = 'Julie'Code: Select all
mysql> explain select * from picture where name = 'Julia' and enabled = 0;
+----+-------------+---------+------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+---------+---------+-------------+------+-------------+
| 1 | SIMPLE | picture | ref | enabled | enabled | 23 | const,const | 1 | Using where |
+----+-------------+---------+------+---------------+---------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from picture where name = 'Julia';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | picture | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql>Code: Select all
alter table picture add index(enable);
alter table picture add index(name);Code: Select all
mysql> explain select * from picture where name = 'Julia';
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | picture | ref | name | name | 22 | const | 1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from picture where name = 'Julia' and enabled = 0;
+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | picture | ref | enabled,name | enabled | 1 | const | 1 | Using where |
+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql>i solved this problem...
first of all here is what mysql docs say:
spesificly this line:
i have this test table:
and
the reason for making the index multi-column is that most of the time i will do something like:
or
that is retriving active or inactive rows...so multi-column index will make such a queries very fast...
but still sometimes on rare occasion i will want to get particular record without any care whether it is active or inactive:
but the problem with last query that mysql can't figure out that it should use multi-column index twice:
first for case enabled=0 and second for case enabled=1...
running EXPLAIN on this query says:
so then i tried:
finaly
i tried:
so db always examines ONLY 2 rows....that is it does what i wanted it to do
by the way other combination such as:
ody, you are suggesting to use separate indexes but it is not good idea in this particular case...alter table picture add index(enable);
alter table picture add index(name);
first of all here is what mysql docs say:
taken from http://dev.mysql.com/doc/refman/5.0/en/explain.htmlref
All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.
spesificly this line:
now, back to my original question...If the key that is used matches only a few rows, this is a good join type
i have this test table:
Code: Select all
id enabled name
--------------------------
5 0 buffy
1 0 jennifer
2 0 john
4 0 marina
3 1 natashaCode: Select all
ALTER TABLE `picture` ADD INDEX ( `enabled` , `name` )the reason for making the index multi-column is that most of the time i will do something like:
Code: Select all
SELECT * FROM picture WHERE enabled=0 AND name='jennifer'Code: Select all
SELECT * FROM picture WHERE enabled=1 AND name='jennifer'but still sometimes on rare occasion i will want to get particular record without any care whether it is active or inactive:
Code: Select all
SELECT * FROM picture WHERE name='jennifer'but the problem with last query that mysql can't figure out that it should use multi-column index twice:
first for case enabled=0 and second for case enabled=1...
running EXPLAIN on this query says:
so db acctually does entire index scan...badid: 1
select_type: SIMPLE
table: picture
type: index
possible_keys: enabledindex
key: enabledindex
key_len: 61
ref:
rows: 5
extra: Using where; Using index
so then i tried:
still with no result...SELECT * FROM picture WHERE (enabled=0 AND name='jennifer') OR (enabled=1 AND name='jennifer')
finaly
running EXPLAIN:SELECT * FROM picture FORCE INDEX (enabledindex) WHERE (enabled=0 AND name='jennifer') OR (enabled=1 AND name='jennifer')
Code: Select all
id: 1
select_type: SIMPLE
table: picture
type: range
possible_keys: enabledindex
key: enabledindex
key_len: 61
ref:
rows: 2
extra: Using where; Using indexby the way other combination such as:
doesn't workSELECT * FROM picture FORCE INDEX (enabledindex) WHERE name='jennifer'