Re: add a correct index, the same select statement becomes 5 times slower

[Rick James]

SELECT  *
    FROM  down_messages
    WHERE  msg_id>=1
      AND  msg_type='4'
      AND  to_id IN ('o1803','all','f2222231',
       'f82006573','o1803|1234','l1','l1|o1803',
       'l1|o1803','l1|o1803','l1|o1803','l1|o1803',
       'l1|o1803','l1|o1803','l1|o1803','l1|o1803',
       'l1|o1803','l1|o1803','l1|o1803')
      AND  begin_time<=NOW()
      AND  end_time>=NOW()
    GROUP BY  msg_id
    ORDER BY  msg_id

I don't see how these are any different to the optimizer:
msg_id>=1
msg_id>0

Depending on how frequently values occur, any of these _could_ be optimal:
INDEX (msg_type, begin_time)
INDEX (msg_type, end_time)
INDEX (msg_type, msg_id)
INDEX (msg_type, to_id)
Add them all, let MySQL decide which to use.
But this one _might_ optimize the GROUP BY and ORDER BY without having to create a tmp table:
INDEX (msg_id, msg_type, to_id)

Why 5x slower?...

First, let's double check. Run the query twice. This avoids caching issues (which typically make a 10x diff).

Then, if it is still 5x slower, consider the effort it takes to use an index:
1. reach into the index, scanning for items of interest
2. for each interesting row, reach (randomly read) into the data
3. check some more things
That's just the filtering. It still has to do the GROUP BY and ORDER BY.

You have 1K rows? What happens with 100K rows?

You are comparing 1ms to 5ms? This is in the noise. Will 5ms break the bank?