index merge vs multi-column index So I have this query:

UPDATE ref_records SET count = count + 1 WHERE ref_url = 'http://www.example.com/' AND user_id = '32366' LIMIT 1


I have indexes set on each "ref_url" and "user_id" respectively. However, MySQL explain shows that it's merging the indexes:

Using intersect(user_id,ref_url); Using where


But why doesn't it do so in their actual column order (ref_url, user_id) ?
Also, would it help optimize this query by adding an index on (ref_url,user_id) or index on (user_id,ref_url) ?

Thanks.
Using intersect is almost always an indication that you should have a "composite" index on the columns mentioned.

In your case, the index can be either of these:

INDEX(user_id, ref_url)
INDEX(ref_url, user_id)


The order of clauses in the WHERE does not matter -- the Optimizer is free to reorder things.  It does not matter to the optimization.

On the other hand, the order of columns in a composite index does matter.  (But it does not happen to matter in this case.)

Further explanation:  For the "intersect" technique, this happens:


Find all entires in INDEX(user_id) for 32366; collect the PRIMARY KEYs for those rows.
Find all entries in INDEX(ref_urs) for 'http...'; collect the PRIMARY KEYs for those rows.
Using those two lists, "intersect" them.  That is, find out what entries are common to both.
Now, reach for the desired rows by using the PRIMARY KEYs.


With a composite index:


Drill down the BTree directly to the desired row(s).