Re: Error while retrieving data in where condition

[Rick James]

Please run
EXPLAIN PARTITION SELECT ...
to see what "pruning" it will do. You may be unpleasantly surprised. I find that SUBPARTITIONs and anything other than simple BY RANGE does not do a good job.

I suspect it is a bug and can be reported to bugs.mysql.com.

Meanwhile, there are several things you can do to improve your schema. (They may or may not work around the bug.)

* Use NOT NULL wherever appropriate. (I see lots of NULLs, even items that are indexed.)
* Provide a PRIMARY KEY -- probably CustRepID, since it is AUTO_INCREMENT. (Meanwhile, the values of CustRepID will probably not be set as you expect!)
* Don't use BIGINT unless you really expect more than 4 billion values.
* Use UNSIGNED wherever appropriate (such as on AUTO_INCREMENT).
* Notice how it avoided the 'perfect' index (KEY `idx_Allquery` (`Custid`,`cid`,`Dt`,`AllPrnt`), ) for your query, but instead did "intersect". Suggest not putting VARCHAR(500) field in indexes.
* More than about 50 partitions --> inefficiency.
* How big is the table? Less than a million rows should not be partitioned.