MySQL query performance using index

indexMySQLperformancequery-performance

77 million records in the Campaign Code table.

Index statement :

INDEX IDX_CampaignCode_Id_CodeDateId_CodeId_CustomerId

(CampaignId, CampaignCodeDateId, PKCampaignCodeId, CustomerId)

First query,
execution time : 0:00:0.546

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 18
AND cc.CampaignCodeDateId = 19325
ORDER BY cc.PKCampaignCodeId LIMIT 20000 OFFSET 0;

enter image description here

Second query,
execution time : 0:01:11.597

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 30
AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

enter image description here

Do not the second question need to be faster? What should I do?

Edit :

I created index for second query.

INDEX IDX_Test_Index

(CampaignId, CustomerId,PKCampaignCodeId)

After index create the second query is faster but first query slower

First query execute plan changed :
execution time : 0:01:17.236
enter image description here

CREATE TABLE CampaignCode (
  PKCampaignCodeId int(11) NOT NULL AUTO_INCREMENT,
  CampaignId int(11) NOT NULL,
  Code varchar(255) NOT NULL,
  CreatedBy int(11) NOT NULL,
  CreatedOn datetime NOT NULL,
  CustomerId int(11) DEFAULT NULL,
  IsActive bit(1) NOT NULL,
  IsUsed bit(1) DEFAULT NULL,
  ModifiedBy int(11) DEFAULT NULL,
  ModifiedOn datetime DEFAULT NULL,
  OrderNumber varchar(255) DEFAULT NULL,
  CampaignCodeDateId int(11) NOT NULL,
  PRIMARY KEY (PKCampaignCodeId),
  INDEX IDX_CampaignCode_CampaignId (CampaignId),
  INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId
            (CampaignId, CampaignCodeDateId, PKCampaignCodeId),
  INDEX IDX_CampaignCode_Code (Code),
  INDEX IDX_Test_Index (CampaignId, CustomerId, PKCampaignCodeId)
)
ENGINE = INNODB
AUTO_INCREMENT = 114306664
AVG_ROW_LENGTH = 61
CHARACTER SET latin5
COLLATE latin5_turkish_ci
ROW_FORMAT = DYNAMIC;

Best Answer

There is no perfect index for this:

WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId

It is because the index cannot get past IS NOT NULL to get to the ORDER BY.

In general, 

WHERE a=1 AND b=2 ORDER BY c

can make good advantage of INDEX(a,b,c) or (b,a,c)

But, this cannot:

WHERE a=1 AND b!=2 ORDER BY c

(IS NOT NULL is similar to != 2 in that it is a "range", not a single value.)

To optimize this:

WHERE cc.CampaignId = 30
  AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

there are two possibilities. The Optimizer will pick one, sometimes the less optimal one:

INDEX(CampaignId, CustomerId)
INDEX(CampaignId, PKCampaignCodeId)

The first will do the filtering, but still have to sort and limit.
The second will do some of the filtering, avoid the sort, but might have to look at a lot more than 25 rows.

There is no benefit of having all 3 columns in an index; the third column will not be used. (And, apparently, it causes trouble with your first SELECT.

More on creating indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql (though it does not fully address all your questions)

Related Question