MySQL query performance using index

Question

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;

• KumarHarsh: BTW , did you drop the first index ?
• RDFozz: As the first index no longer shows up in Possible Keys, presumably the OP did drop it.
• Rick James: Please provide SHOW CREATE TABLE. I need to see if there are only 4 columns, and to see the datatypes.
• Yunus UYANIK: @RickJames edit my question. KumarHarsh no, just drop column CustomerId
• KumarHarsh: is your problem solved ?what finally work for you ?

Answer 1

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)

• KumarHarsh: I forgot about "Range". given link is terrific .I think in this situation sometime even changing the order of where condition start utilising index.without actually creating new index.
• Rick James: WHERE a AND b is no different than WHERE b AND a. But INDEX(a,b) is different than INDEX(b,a).
• Yunus UYANIK: Thank you for answer, I created the indexes you suggested. Nice work.

Answer 2

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

No second query won't utilize the index because of ordering and column in where condition matter in composite non clustered index.

So in order to utilize index on second query you should another composite non clustered index.

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

Also avoid using *,use column name instead.

• Yunus UYANIK: I created an index as you said. The results are on top.

Answer 3

The second query is not even using the index, instead is using the PRIMARY KEY. If you don't have much write on the table you may keep both indexes so INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId (CampaignId, CampaignCodeDateId, PKCampaignCodeId) and INDEX IDX_Test_Index (CampaignId, CustomerId, PKCampaignCodeId).

And if you need an additional advice the index here: INDEX IDX_CampaignCode_CampaignId (CampaignId) is redundant. It will not be used and you may drop it.