Comments - Query on partition with like take forever
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
What is the output if you run EXPLAIN on the two queries?
Here the results:
MariaDB [DRS_DB]> explain select * from cdr_03 where TimeStamp >= "2018-01-01 00:00:00" -> and TimeStamp <= "2018-01-01 23:59:59" and IP = "343.456.989.659"; +------+-------------+--------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | cdr_03 | ref | IP | IP | 23 | const | 2300 | Using where | +------+-------------+--------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (3.931 sec) MariaDB [DRS_DB]> explain select * from cdr_03 where TimeStamp >= "2018-01-01 00:00:00" -> and TimeStamp <= "2018-01-01 23:59:59" and IP like "343.456.989.65%"; +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | cdr_03 | range | IP | IP | 23 | NULL | 2300 | Using where | +------+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (3.733 sec)I agree that this looks implausibly slow. I'd raise a bug report if there isn't already.
Could it be you are thrashing a table cache of insufficient size for 600-2300 partitions?
Your structure does break a few of the guidelines on partitioning.
Also FYI INET6 datatype does exist in 10.5+ version.