Re: Table index performance. options about choosing primary key
Posted by: Rick James
Date: November 25, 2011 01:34PM

> 1/ which one is better: OPTION A: set the unique attribute of each table to be the table's primary key? or OPTION B: create another attribute to be an id (auto number) and set the table's unique attribute with unique constraint,.? (will be applied to all those three tables).
It depends.
Is this InnoDB? Or MyISAM?
Is there another index on the table?
Do you ever do something like
SELECT A1 FROM TBL_A WHERE A2 = '...';
Probably it is better for the UNIQUE key to be the PRIMARY KEY. InnoDB will do that automatically.

I assume TBL_AB is the many-to-many mapping between TBL_A and TBL_B? And it has the PRIMARY KEYs of TBL_A and TBL_B? An AUTO_INCREMENT would be much less bulky, hence might be preferable. How many rows will eventually be in each table? How much RAM do you have? (Cacheability factors into the equations.)

> 3. because od TBL_C will refers to TBL_AB's primary key
Now you lost me.

> will querying an integer (or numeric) type primary key perform faster than varchar type
utf8? Some other CHARACTER SET?
Size of tables? -- This leads to whether we are talking about disk I/O or CPU time.
Are the VARCHAR(64) values some kind of MD5 or SHA1 or GUID or UUID? If so, we need to discuss issues with such.

Options: ReplyQuote


Subject
Written By
Posted
Re: Table index performance. options about choosing primary key
November 25, 2011 01:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.