This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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

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.