NYCPHP Meetup

[Daniel Convissor]

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...

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


--Dan

-- 
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409