Hi there!

My name is Lynea. I'm new to the CF and I'm happy to be here.

I'm also new to MySQL. I come from a SQL Server background mainly as a BI Developer. On a daily basis, I normally would create queries that were quite complex, or edit existing ones (the last one, before the company was closed, was 25-pages long). This isn't to brag, but to contrast to the fact that now that I am learning MySQL, I'm finding myself befuddled by rather simple queries.

My focus on this job is further optimizing existing queries. What of the things I've learned is that MySQL runs in such a way that it makes a ton of assumptions on the intention of the developer by the code, and then just processes according to the assumption. Some of these are slightly intuitive, and some not so much.

With all this in mind, I thought it *might* by useful to sort of brainstorm and create a somewhat comprehensive list of at least COMMON causes of what would cause a query in MySQL to ignore an index. I was hoping that other MySQL developers might be willing to add to the discussion, or even provide further clarification of the ones I'll start off listing (as I've just learned about them).

We'll start with the blatantly obvious ones:


1.) Joins on fields of differing data types

2.) Length of field being index is not complete, but referenced or selected partially.

3.) If the fields are just in the SELECT, but the field is not specified in a WHERE clause, or anywhere else where the field is making the results more selective (outside of the SELECT statement, including the ORDER BY).

4.) If the column accepts NULL values, yet, there is nothing that states explicitly whether or not your query ought to exclude these. This, apparently, bypasses the index and does a full table scan.




What others can we add or alter here?