Page 1 of 1

mysql index decision

Posted: Fri Mar 10, 2006 5:04 am
by newmember
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

Posted: Fri Mar 10, 2006 8:40 am
by feyd
I think that depends on how you actually created the index. It'd be helpful to see the actual SQL code to recreate your table.

Posted: Fri Mar 10, 2006 8:45 am
by newmember
feyd wrote: how you actually created the index.
with phpmyadmin

Posted: Fri Mar 10, 2006 9:06 am
by feyd
That doesn't tell me anything. Post the SQL table creation code.

Posted: Fri Mar 10, 2006 11:21 am
by newmember

Code: Select all

CREATE TABLE `picture` (
`id` INT NOT NULL AUTO_INCREMENT ,
`enabled` TINYINT NOT NULL ,
`name` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `id` ) 
)
and then

Code: Select all

ALTER TABLE `picture` ADD INDEX ( `enabled` , `name` )

Posted: Fri Mar 10, 2006 11:51 am
by feyd
Hmm, not entirely sure how MySQL will work with it, but you can find out:

Code: Select all

EXPLAIN SELECT `id` FROM `picture` WHERE `name` = 'Julie'
run that in phpMyAdmin

Posted: Sat Mar 11, 2006 6:07 am
by ody

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>
So NO, the reason being the index is a multiple-column index (both values are concatenated and only get used when your search is based on enable AND name), try instead doing:

Code: Select all

alter table picture add index(enable);
alter table picture add index(name);
And we now get..

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>
Now thats better.

Posted: Sat Mar 11, 2006 11:17 am
by newmember
i solved this problem...

alter table picture add index(enable);
alter table picture add index(name);
ody, you are suggesting to use separate indexes but it is not good idea in this particular case...

first of all here is what mysql docs say:

ref

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.
taken from http://dev.mysql.com/doc/refman/5.0/en/explain.html

spesificly this line:
If the key that is used matches only a few rows, this is a good join type
now, back to my original question...
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           natasha
and

Code: 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'
or

Code: Select all

SELECT * FROM picture WHERE  enabled=1 AND name='jennifer'
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:

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:
id: 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 db acctually does entire index scan...bad

so then i tried:
SELECT * FROM picture WHERE (enabled=0 AND name='jennifer') OR (enabled=1 AND name='jennifer')
still with no result...
finaly :idea: i tried:
SELECT * FROM picture FORCE INDEX (enabledindex) WHERE (enabled=0 AND name='jennifer') OR (enabled=1 AND name='jennifer')
running EXPLAIN:

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 index
so db always examines ONLY 2 rows....that is it does what i wanted it to do :D

by the way other combination such as:
SELECT * FROM picture FORCE INDEX (enabledindex) WHERE name='jennifer'
doesn't work