Question 21410 in Mysql Question posted in
The official MySQL documentation can be found here.

Add index when allready is primary – Mysql

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 ?

enter image description here

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search