Quote
If the 'between' statement on the date field can't be avoided:
Did I understand it right that the query breaks at that 'index position' where the Date column is defined? ((not quite)) Then it should be placed at the end of that composite key ((generally, yes)) ... or is it also an alternative creating a single/extra index for that date field. ((useless))
* If you are constructing the WHERE clause via code, turn it into an '=' when possible.
* DATE, DATETIME, TIME, and TIMESTAMP are just like INT under the hood. The important difference is "=" versus any other flavor of comparision ("<", BETWEEN, ...).
* When designing an INDEX, put "=" field(s) first, then you get one shot at a non-"=", GROUP BY or ORDER BY. So, if your DATE can be BETWEEN, then it should be last.
* MySQL will (usually) use only one INDEX per SELECT. So adding another index won't help. (OK, it might be useful if the new index is deemed better and can be used _instead of_ the current index.)