Add index when allready is primary

Question

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 ?

Does this answer your question? https://stackoverflow.com/questions/12222630/can-mysql-use-multiple-indexes-for-a-single-query — Neville Kuyt, Jan 06 '23 at 09:32
We need to see `SHOW CREATE TABLE` for both tables; there are some subtle issues that are not clear. — Rick James, Jan 07 '23 at 16:09

score 0 · Answer 1 · answered Jan 06 '23 at 17:48

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.

score 0 · Answer 2 · answered Jan 07 '23 at 16:17

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.)

Add index when allready is primary

2 Answers2