Trying to simplify the context for this problem, but let me know if you require additional information.

I have a table named base with several columns, including a field named id.

Each field has a BTREE index created via create index X on base(X)

I've found that queries of the type select id from base where X = 'value1' and Y = 'value2' do not return the correct number of results.

If I do a select id, X from base where Y = 'value2' order by X, I can see a full and accurate list of all combinations of X and Y = 'value2', since this query does not execute an index_merge (running an explain on this shows that its a ref query using the index on Y).

But running select id from base where X = 'value1' and Y = 'value2' clearly shows that some rows are dropped from the results when compared to what you see with select id, X from base where Y = 'value2' order by X. Running an explain shows that an index_merge is run on the indices on both X and Y.

Rebuilding the indices does not fix the problem, and results in the same rows consistently being dropped from the query results.

I can provide firmer context with actual query outputs if this is too abstract. Thanks for your help.