Query on partition with like take forever

HI, I created a table like this:

Create Table: 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.

From the documentation I read the like will use the partitioning and also the index, so I cannot understand why is not working. Thanks

Comments

Comments loading...
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.