How to speed up conditional MYSQL query

user_10278643 0
Viewed 83

Query is:

SELECT RP.pID
  , (IF(RI.p01 = 54, 1, 0)
     + IF(RI.p02 = 16, 1, 0)
     + IF(RI.p03 = 54, 1, 0)
     + IF(RI.p04 = 92, 1, 0)
     + IF(RI.p05 = 34, 1, 0)
     + IF(RI.p06 = 51, 1, 0)
     + IF(RI.p07 = 62, 1, 0)
     + IF(RI.p08 = 98, 1, 0)
     + IF(RI.p09 = 14, 1, 0)
     + IF(RI.p10 = 25, 1, 0)
     + IF(RI.p11 = 34, 1, 0)
     + IF(RI.p12 = 67, 1, 0)
     + IF(RI.p13 = 81, 1, 0)
     + IF(RI.p14 = 29, 1, 0)
     + IF(RI.p15 = 24, 1, 0)
     + IF(RI.p16 = 45, 1, 0)
     + IF(RI.p17 = 72, 1, 0)
     + IF(RI.p18 = 86, 1, 0)
     + IF(RI.p19 = 25, 1, 0)
     + IF(RI.p20 = 95, 1, 0)
     + IF(RI.p21 = 92, 1, 0)
     + IF(RI.p22 = 31, 1, 0)
     + IF(RI.p23 = 24, 1, 0)
     + IF(RI.p24 = 78, 1, 0)) AS ATTP
FROM RP
LEFT JOIN RI
    ON RP.rID = RI.rID
    AND RP.nID = '1'
WHERE RI.p25 > ((RI.p27 + RI.p26) * 8)
ORDER BY ATTP DESC LIMIT 1000000;

In real situation there are 27 of such ifs and query takes 25-35 seconds to complete.

Table in which I check these conditions is relational and grows up everyday by ~50 000 records.

To speedup WHERE part of query I have Primary index on iNR, but how to speedup these IFS?

Execution plan:

enter image description here

Table structure:

CREATE TABLE `RI` (
  `rID` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
  `p01` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p02` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p03` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p04` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p05` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p06` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p07` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p08` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p09` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p10` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p11` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p12` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p13` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p14` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p15` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p16` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p17` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p18` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p19` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p20` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p21` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p22` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p23` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p24` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p25` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p26` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `p27` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `ils` mediumblob NOT NULL,
  PRIMARY KEY (`rID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_lithuanian_ci;

CREATE TABLE `RP` (
  `pID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `rID` mediumint(8) unsigned NOT NULL,
  `nID` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`pID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_lithuanian_ci;
mysql
2 Answers

Fix 1 :Indexing all 4 columns a,b,c, & d will help processing faster.

Fix 2 : May be adding a trigger which updates another column in the same table when value is inserted or updated will solve the problem.

both fix are temporary ones please provide more info on the problem statement on why you need the if's in query hope it helps

user_18848590

Now when we got real query and DDL, it makes it so much easier to help you! :)

Try creating the following index:

CREATE INDEX IX_RP_nID_rID ON RP (nID, rID);

This should eliminate full RP table scan that is now happening. It should add a significant boost but there still may be room for improvement.

Also, I think that your LEFT JOIN behaves as INNER JOIN because you still have a filtering condition in WHERE clause.

I saw that InnoDB let's you index expressions. If you're always querying exactly same columns, you could try creating an index on RI table that would include rID, p25 ((p27 + p.26) * 8) and whole expression in select statement. This technically should persist calculation.

But this solution will work later, after mysql update https://dev.mysql.com/doc/refman/8.0/en/create-index.html

user_36800980
Related