Re: Poor Performance with tables over 1Million entries

[Rick James]

What is innodb_buffer_pool_size? It should be around 70% of available RAM.

46 rows (estimated) for doing that query -- that is nice. Sounds as if it did the OR efficiently. Since it needs to read about that many rows to get the answer, you aren't going to get it much faster with out overhauling your schema and application.

"Using index" does not mean what you might think. It means that the query was satisfied by using ONLY the index for that table. That is, it did not need to reach over into the data. This happens with it has an index containing ALL the fields in the SELECT fields, WHERE, ORDER BY, etc.

If you are referring to SELECT * on that mapping table, I agree that it is strange -- seems like it should discover that it could be "using index". Perhaps it is, but fails to say so??