MySql Order by isnull() Performance Problem

indexMySQLnullorder-byperformance

my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.

AddDate and Price has an index.

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
order by isnull(Price), Price asc limit 50

Explain the sql shown that it is not using the Price index.
So i tried to improve the query and has come out with below sql

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
and Price is not null
order by Price asc limit 50

The new sql works a lot faster and explain shown it uses Price index but the problem is that Price with null values will never selected.

Looking for any comments or suggestions on how to solve this problem. Thanks.

Best Answer

Your original clause:

order by isnull(Price), Price asc limit 50

Uses a function on the Price column. In your case, the function is isnull(); but it does not matter - any function on your column, e.g. COALESCE(Price, ...), Price + 1, etc., would make for the same result.

You see, once there's a function on a column, MySQL is then unable to use that column. Consider: the index uses a BTREE on Price, indexing your millions of rows in ascending order. But your request, to order by isnull(Price) requests an altogether different thing: you just want to provide with NULLs or not-NULLs; well, MySQL will not use the index for that.

I suppose it would be nice of MySQL to recognize a set of "safe" functions, which are monotonic with the index; but MySQL gives up an index the moment it sees a function. It just does not predict how the result of the function looks like.

Other databases support Index functions; the MariaDB fork supports virtual columns and indexes on virtual columns -- both solution can help you out with your query. Plain MySQL just can't eat it.

Related Solutions

MySQL 5.1 -> 5.6 problem, select distinct order by limit query stops using index

I think I've found a workaround, though I'm going to file a bug report unless someone can explain why this works.

The problem appears to originate with the id portion of the order by clause. When using either

order by name asc,
         name asc,
         id asc

order by name asc,
         id asc,
         id asc

then the query uses the existing indices on related_id and the results come back instantly. I had another similar query, and it was solved by changing this

order by id asc

to this

order by id asc,
         id asc

This makes absolutely no sense to me, but it works, and it all seems to be focused on the presence of order by id.

Mysql – Index not used on partitioned table

There are a few problems here:

First, as jkavalik says in the comments on the OP, the order of columns in an index matters. Basically, in your case for index_rqcd to be used for filtering on rq_date, t_id has to be used before it can "see" and filter on rq_date. Since usually only one range scan on an index can be done for a query and it has to be the last part of the index used, the optimizer correctly skips using this index because it would probably have to use the entire index which cause a full scan on the index and then a full scan on the table, or in this case partition. If the table and had an index starting with rq_date, that index would likely be used.

Second, something you should be aware of when using partitions is that MySQL doesn't have global indexes for partitioned tables. What this means is that each partition has its own indexes and therefore can only be used inside that partition. Since the optimizer knows that the query only needs to use partition p736467 based on the query and table definition (an optimization known as partition pruning), only that partition, and in your case the associated sub-partitions, are considered when running the query. So now that the optimizer is only using partition p736467, it can consider using index_rqcd, but since it is still affected by the first problem and its use would lead to a full scan on the index, it chooses to do a full scan on the data. Even though the EXPLAIN shows ALL for the type, it is only scanning the data in the p736467 partition, not the entire table, because of the partition pruning. Even if you add an index starting with rq_date, it will not be used if you want to get all of the rows for the day the partition is for. However, the query you give will only return rows for exactly 1 day ago down to the second, not the whole day as you say when describing what you want to do. If you really do want to get rows for a specific time and not the whole day, and index starting with rq_date will be useful.

Probably the most important thing here is that, as mentioned by the link jkavalik posted, you shouldn't use partitions unless you have a good reason to. Time ranges can be a valid use for partitions, but usually only when used with a sliding window of chronological data, e.g. 7 days of log messages that are pruned daily. Generally unless you can use partition pruning in (almost) every query, they will hurt performance since every partition will likely need to be accessed. If you are always querying this table for rows on a specific date and then filtering on a range on some other indexed column, partitions might be useful. More likely, partitions are not the right way to go. Unless you have a very good reason to use the sub-partitions, they are almost certainly just adding overhead to every query.

My suggestion, assuming that most of your queries are based on a range of rq_date, is to not use partitions and add an index starting with rq_date.

Also, when using InnoDB, you should always define a primary key. I don't know about the data you're storing, but it looks like rq_id might be unique and a good candidate.

Best Answer

Related Solutions

MySQL 5.1 -> 5.6 problem, select distinct order by limit query stops using index

Mysql – Index not used on partitioned table

Related Question