this is myTable

clientId, itemId, sellId

primary is on clientId and there is also btree index no uniqe on sellId

now i have part of very slow query

LEFT OUTER JOIN myTable wl ON wl.itemId= ld.itemId and wl.clientId= @clientId

question is should i create here index combined for both clientId and itemId or since clientId is primary then only for itemId ?

CodePudding user response：

Your question is this: should I put the primary key PK of the table into a multi-column index as the last column?

If you use the InnoDB storage engine, the answer is no. Why not? InnoDB already includes the PK as part of the index.

If you don't use InnoDB -- that is, if you use MyISAM or AriaDB (in MariaDB), the answer is yes.

Still, you should evaluate how well the index helps your query.

CodePudding user response：

ON wl.itemId= ld.itemId and wl.clientId= @clientId

begs for

INDEX(itemId, clientId) -- in either order

However, if either of those columns is the PRIMARY KEY of wl, then no index is needed, nor useful. The PK will provide immediate access to the needed row. The other column cannot do anything other than verify that it matches -- that is eliminate the row from the JOIN.

should i create here index combined for both clientId and itemId

Yes, if neither is UNIQUE (Keep in mind that the PK is 'unique'.)

or since clientId is primary then only for itemId

Almost never will MySQL use two separate indexes. (Keep in mind that the PK is an index.)