Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.9.3
-
AWS server m6g.2xlarge, centos 8, with Galera on a 3 node cluster
Description
I created a table like this:
CREATE TABLE `cdr_03` (
|
`TimeStamp` datetime DEFAULT NULL,
|
`Ses` varchar(10) DEFAULT NULL, |
`Session` varchar(50) DEFAULT NULL, |
`IP` varchar(20) DEFAULT NULL, |
`IMSI` varchar(30) DEFAULT NULL, |
`APN` varchar(300) DEFAULT NULL, |
`MCC` int(11) DEFAULT NULL, |
`MNC` int(11) DEFAULT NULL, |
`ECGI` int(11) DEFAULT NULL, |
`TAI` int(11) DEFAULT NULL, |
`NAS` varchar(30) DEFAULT NULL, |
`IMEI` varchar(30) DEFAULT NULL, |
`UPD` datetime DEFAULT NULL,
|
KEY `IP` (`IP`),
|
KEY `IMSI` (`IMSI`),
|
KEY `APN` (`APN`),
|
KEY `IMEI` (`IMEI`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
PARTITION BY HASH (to_days(`TimeStamp`))
|
PARTITIONS 2300 |
then I loaded about 600 partitions with 2M records each.
using this query:
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"; |
I get a reply with one record in about 8 seconds.
using this query:
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%"; |
after 48 hours the query still running. There is only 1 record in that interval and the wild card is covering only 1 char.
the explain of the 2 queries is:
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 | |
+------+-------------+--------+------+---------------+------+---------+-------+------+-------------+
|
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 | |
+------+-------------+--------+-------+---------------+------+---------+------+------+-------------+
|
The ref field, in the query using the like, is set to NULL, but according to the documentation, the like is supposed to use the index if the % is not at the beginning.
Also, there is only 1 record in that interval and the partition contains only 2M rows.