MySQL is not correctly selecting rows (sometimes)
Based on the comment that using FORCE KEY alters the result from the query, it is very likely that we are dealing with the merge optimizer bug. EXPLAIN of the original query shows the optimization is done by selecting from the deleted key, then from the post_id key, then merging the results. When we force to bypass that code, the problem goes away.
The steps from the point:
- try it on the same data with the most recent 5.6 version of MySQL
- if the issue reproduces, try to isolate it to the most minimal test case, visit http://bugs.mysql.com/ and report the bug