query optimization - Instructing MySQL to apply WHERE clause to rows returned by previous WHERE clause -

i have following query:

  select dt_stamp     claim_notes    type_id             = 0      , dt_stamp           >= :dt_stamp      , date( dt_stamp )    = :date      , user_id             = :user_id      , note             :click_to_call    order dt_stamp    limit 1 

the claim_notes table has half million rows, query runs since has search against unindexed note column (which can't about). know when type_id, dt_stamp, , user_id conditions applied, i'll searching against 60 rows instead of half million. mysql doesn't seem apply these in order. i'd see if there's way tell mysql apply note :click_to_call condition rows meet former conditions it's not searching rows condition.

what i've come this:

  select dt_stamp     (             select *               claim_notes              type_id           = 0                , dt_stamp         >= :dt_stamp                , date( dt_stamp )  = :date                , user_id           = :user_id          )      , note :click_to_call    order dt_stamp    limit 1 

this works , extremely fast. i'm wondering if right way this, or if there more official way handle it.

it shouldn't necessary this. mysql optimizer can handle if have multiple terms in clause separated and. basically, knows how "apply conditions can using indexes, apply unindexed expressions only remaining rows."

but choosing right index important. multi-column index best series of and terms individual indexes. mysql can apply index intersection, that's less effective finding same rows single index.

a few logical rules apply creating multi-column indexes:

• conditions on unique columns preferred on conditions on non-unique columns.

• equality conditions (=) preferred on ranges (>=, in, between, !=, etc.).

• after first column in index used range condition, subsequent columns won't use index.

• most of time, searching result of function on column (e.g. date(dt_stamp)) won't use index. it'd better in case store date data type , use = instead of >=.

• if condition matches > 20% of table, mysql decide skip index , table-scan anyway.

here webinars myself , colleagues @ percona explain index design:

• tools , techniques index design
• mysql indexing: best practices
• advanced mysql query tuning
• really large queries: advanced optimization techniques

you can slides these webinars free, , view recording free, recording requires registration.