NYCPHP Meetup

[Rob Marscher]

On Mar 23, 2010, at 11:50 AM, Daniel Convissor wrote:
> On Tue, Mar 23, 2010 at 11:12:57AM -0400, Rob Marscher wrote:
>> 
>> I'm having trouble finding exactly where it says it.
> ...
>> If you run EXPLAIN, you'll see it only picks one index to use for each 
>> table.
> 
> That may be true for the queries you ran.  If that's the case, you don't 
> have enough rows for the optimizer to realize that using more than one 
> indexes makes a difference.  Once you do, your EXPLAIN output will look 
> something like this...

You have two tables here and you'll see the second table is using the "index_merge" optimization that I mentioned.  So you're right, there is a few ways that mysql can use more than one index.

If you don't have enough rows, then the index is probably not being used because mysql only uses the indexes when they provide a significant enhancement over a full table scan (I know... throwing out another statement without a reference, but I think this is stated in the documentation page I send on how mysql uses indexes).

> EXPLAIN
> SELECT person_id
> FROM person
> JOIN person_status USING (status_id)
> WHERE department_id = 11 AND status_id = 10 \G
> 
> *************************** 1. row ***************************
>           id: 1
>  select_type: SIMPLE
>        table: person_status
>         type: const
> possible_keys: PRIMARY
>          key: PRIMARY
>      key_len: 4
>          ref: const
>         rows: 1
>        Extra: Using index
> *************************** 2. row ***************************
>           id: 1
>  select_type: SIMPLE
>        table: person
>         type: index_merge
> possible_keys: status_id,department_id
>          key: department_id,status_id
>      key_len: 4,5
>          ref: NULL
>         rows: 1
>        Extra: Using intersect(department_id,status_id); Using
>               where; Using index