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

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
by the way other combination such as:
SELECT * FROM picture FORCE INDEX (enabledindex) WHERE name='jennifer'
doesn't work