Ytukyg

I'm getting some strange timing values from Mysql running a "simple" query.

This is the DDL of the table:

CREATE TABLE `frame` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `createdBy` varchar(255) DEFAULT NULL,

  `createdDate` datetime(6) NOT NULL,

  `lastModifiedBy` varchar(255) DEFAULT NULL,

  `lastModifiedDate` datetime(6) DEFAULT NULL,

  `sid` varchar(36) NOT NULL,

  `version` bigint(20) NOT NULL,

  `brand` varchar(255) DEFAULT NULL,

  `category` varchar(255) DEFAULT NULL,

  `colorCode` varchar(255) DEFAULT NULL,

  `colorDescription` varchar(255) DEFAULT NULL,

  `description` longtext,

  `imageUrl` varchar(255) DEFAULT NULL,

  `lastPurchase` datetime(6) DEFAULT NULL,

  `lastPurchasePrice` decimal(19,2) DEFAULT NULL,

  `lastSell` datetime(6) DEFAULT NULL,

  `lastSellPrice` decimal(19,2) DEFAULT NULL,

  `line` varchar(255) DEFAULT NULL,

  `manufacturer` varchar(255) DEFAULT NULL,

  `manufacturerCode` varchar(255) DEFAULT NULL,

  `name` varchar(255) NOT NULL,

  `preset` bit(1) NOT NULL DEFAULT b'0',

  `purchasePrice` decimal(19,2) DEFAULT NULL,

  `salesPrice` decimal(19,2) DEFAULT NULL,

  `sku` varchar(255) NOT NULL,

  `stock` bit(1) NOT NULL DEFAULT b'1',

  `thumbUrl` varchar(255) DEFAULT NULL,

  `upc` varchar(255) DEFAULT NULL,

  `arm` int(11) DEFAULT NULL,

  `bridge` int(11) DEFAULT NULL,

  `caliber` int(11) DEFAULT NULL,

  `gender` varchar(255) DEFAULT NULL,

  `lensColor` varchar(255) DEFAULT NULL,

  `material` varchar(255) DEFAULT NULL,

  `model` varchar(255) NOT NULL,

  `sphere` decimal(10,2) DEFAULT NULL,

  `type` varchar(255) NOT NULL,

  `taxRate_id` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `UK_k7s4esovkoacsc264bcjrre13` (`sid`),

  UNIQUE KEY `UK_ajh6mr6a6qg6mgy8t9nevdym1` (`sku`),

  UNIQUE KEY `UK_boqikmg9o89j8q0o5ujkj33b3` (`upc`),

  KEY `idx_manufacturer` (`manufacturer`),

  KEY `idx_brand` (`brand`),

  KEY `idx_line` (`line`),

  KEY `idx_colorcode` (`colorCode`),

  KEY `idx_preset` (`preset`),

  KEY `idx_manufacturer_model_color_caliber` (`manufacturer`,`model`,`colorCode`,`caliber`),

  KEY `FK1nau29fd70s1nq905dgs6ft85` (`taxRate_id`),

  CONSTRAINT `FK1nau29fd70s1nq905dgs6ft85` FOREIGN KEY (`taxRate_id`) REFERENCES `taxrate` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=392179 DEFAULT CHARSET=utf8;

The query is created programatically from my application. The "strange" syntax (NULL IS NULL OR condition) is very convenient to me in order to make more compact my code and removing the need to create a different query based on the numbers of parameters.

For who understand how Hibernate HQL and JPA works, this is the query:

This query is generated when the user is not setting any filter, so all parameters in my condition are null and this is how the query comes out.

SELECT SQL_NO_CACHE COUNT(frame0_.`id`) AS col_0_0_ FROM `Frame` frame0_ 

WHERE (NULL IS NULL OR NULL LIKE CONCAT('%', NULL, '%') OR frame0_.`manufacturer` LIKE CONCAT('%', NULL, '%') OR frame0_.`manufacturerCode`=NULL OR frame0_.`sku`=NULL OR frame0_.`upc`=NULL OR frame0_.`line` LIKE CONCAT('%', NULL, '%') OR frame0_.`model` LIKE CONCAT('%', NULL, '%')) AND (NULL IS NULL OR frame0_.`manufacturer`=NULL) AND (NULL IS NULL OR frame0_.`line`=NULL) AND (NULL IS NULL OR frame0_.`caliber`=NULL) AND (NULL IS NULL OR frame0_.`type`=NULL) AND (NULL IS NULL OR frame0_.`material`=NULL) AND (NULL IS NULL OR frame0_.`model`=NULL) AND (NULL IS NULL OR frame0_.`colorCode`=NULL)

The query takes about 0.105s on a table of 137548 rows.
The EXPLAIN of the previous query returns:

id  select_type table   partitions  type    possible_keys   key key_len ref rows     filtered   Extra

1   SIMPLE       frame0_      N             ALL        N  N  N  N      137548   100.00   N

The previous query is identical to this one:

SELECT SQL_NO_CACHE COUNT(frame0_.`id`) AS col_0_0_ FROM `Frame` frame0_

This query takes just 0.05s for the same result in the same table.

Why for Mysql they are different and the first is taking so much time? Is there a way to improve performance of the first query keeping the syntax "NULL IS NULL or condition"?

I think Ryan has right, so the many or statement made the query so bad.

You should programatically build queries for better performance. So if the user not select by a possible filter, than you shouldn't include to the query!

(HQL)

if(!StringUtils.isEmpty(manufacturer)) {

  query.and(m.manufacturer.eq(manufacturer))

}