Jump to content
StevenOliver

mySQL and rebuilding keys

Recommended Posts

Question 1.) When I change a record in a mySQL table with a key, how come I don't need to delete and recreate the entire key?

I always assumed that a "key" is some sort of super-condensed index that probably gets loaded into mySQL memory. If that's the case, how come making changes to the records (even small changes) doesn't throw the entire key off?

Question 2.) I've seen 2 ways of adding a primary  key. Using an example column "merchandise," what are the differences and which way is better:
a.) ALTER TABLE mytable ADD PRIMARY KEY (merchandise)
b.) ALTER TABLE mytable ADD PRIMARY KEY merchandise (merchandise)

Thank you!

 

Share this post


Link to post
Share on other sites

1. Because MySQL updates the index automatically. After all, having to manually rebuild the index every time data changes would be incredibly tedious.

2. Same thing. One key is named automatically, one key is named manually. And no, the name does not matter. But adding the primary key after the table is created is annoying - do it when the table is created, inside the table definition.

Share this post


Link to post
Share on other sites
Posted (edited)

Requinix, thank you for your answers. Would you please clarify the "mySQL updates the index automatically" part?
Given a database with 100000+ records:
1.) with Primary Key, takes .001 seconds to do a lookup.
2.) without Primary Key, takes 20 seconds to do a lookup.
3.) after removing primary key, "Add primary key" takes about 1 minute.

If I alter a record, I can immediately do another lookup and it will only take .001 seconds. That must mean mySQL doesn't re-index the entire table when updating the index (because reindexing takes an entire minute, and if my table isn't reindexed yet, the lookup would take forever...). I wonder how and where it updates the index?

Would you please clarify your "one key is named automatically" answer? What exactly is happening when I issue this command:
ALTER TABLE mytable ADD PRIMARY KEY merchandise (merchandise)

(I'm still not understanding the difference between "...add primary key columnName (columnName)" and "...add primary key (columnName)"

Thank you!!

 

Edited by StevenOliver

Share this post


Link to post
Share on other sites
1 hour ago, StevenOliver said:

Requinix, thank you for your answers. Would you please clarify the "mySQL updates the index automatically" part?

Given a database with 100000+ records:
1.) with Primary Key, takes .001 seconds to do a lookup.
2.) without Primary Key, takes 20 seconds to do a lookup.
3.) after removing primary key, "Add primary key" takes about 1 minute.

If I alter a record, I can immediately do another lookup and it will only take .001 seconds. That must mean mySQL doesn't re-index the entire table when updating the index (because reindexing takes an entire minute, and if my table isn't reindexed yet, the lookup would take forever...). I wonder how and where it updates the index?

This may be a bit more technical than you're expecting, but it answers essentially what you're asking about.

1 hour ago, StevenOliver said:

Would you please clarify your "one key is named automatically" answer? What exactly is happening when I issue this command:
ALTER TABLE mytable ADD PRIMARY KEY merchandise (merchandise)

(I'm still not understanding the difference between "...add primary key columnName (columnName)" and "...add primary key (columnName)"

Thank you!!

Have MySQL describe the table and its indexes for you after each command. Look for the difference.

Share this post


Link to post
Share on other sites
Posted (edited)

Requinix, thank you!! That is exactly what I was looking for. (It actually talks about exactly what I'm asking -- what happens when you delete values, etc.). Thank you!!

Regarding question 2... (good idea, by the way.... try both then describe the tables!)....

So.... I just now created two tables, one with "...primary key (col3)" and the other with "...primary key col3 (col3)" but they both look the same, so I am still not quite sure what the difference is:

describe Table1
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1   | varchar(43) | YES  |     | NULL    |       |
| col2  | varchar(3) | NO   | PRI | NULL    |       |
| col3 | varchar(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

describe Table2
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| col1   | varchar(43) | YES  |     | NULL    |       |
| col2  | varchar(3) | NO   | PRI | NULL    |       |
| col3 | varchar(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

I also ran the command "show index from Table1" and it was identical to Table2's index.

Thank you.

Edited by StevenOliver

Share this post


Link to post
Share on other sites

Try more like a

SHOW INDEX FROM TableX

SHOW CREATE TABLE TableX

 

Share this post


Link to post
Share on other sites

Hmm... I did that, the results are absolutely identical.

I created the tables like this:

Create table Table1(
col1 varchar(3),
col2 varchar(3),
primary key col2 (col2)
);

Create table Table2(
col1 varchar(3),
col2 varchar(3),
primary key (col2)
);

What would you think I should be seeing that I'm missing?

Share this post


Link to post
Share on other sites

Okay, so I thought a SHOW CREATE TABLE would include the indexes.

Do the other SHOW INDEX FROM.

Share this post


Link to post
Share on other sites

I did -- I did "show index from Table1" and the result was identical to "show index from Table2." 

Share this post


Link to post
Share on other sites

Are you sure? What was the output?

Share this post


Link to post
Share on other sites

show index from Table1;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table1 |          0 | PRIMARY  |            1 | col2        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.007 sec)

show index from Table2;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table2 |          0 | PRIMARY  |            1 | col2        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.007 sec)

Share this post


Link to post
Share on other sites

Where there any warnings with the "ALTER TABLE mytable ADD PRIMARY KEY merchandise (merchandise)" statement?

Share this post


Link to post
Share on other sites

No -- no warnings.

I created the tables with both "primary key col2 (col2)" and "primary key (col2)" and there were no warnings.

Then I tried the "alter table" syntax after dropping the primary keys:
"alter table Table1 add primary key col2 (col2)"
-and-
"alter table Table1 add primary key (col2)"

... and neither of those gave any warnings. 

For all intents and purposes, they seem 100% identical.

Perhaps in the olden days the "...primary key col2 (col2)" was how it used to be done, then it got deprecated and now "...primary key (col2)" is how it's done today, with both commands having the same effect.

What are your thoughts?

Share this post


Link to post
Share on other sites

For a regular index, that "col2" would be the name of it. For a primary key (which I forgot was different) that doesn't use a name, that "col2" would be the index type. Of course "col2" and "merchandise" are not valid index types so I would have expected at least a warning. And I don't have a MySQL lying around to test with.

So I'm thinking it's (accidentally?) ignored for sake of compatibility with other types of indexes.

Share this post


Link to post
Share on other sites
Posted (edited)

Primary keys do not require a name. Other indexes do. If you do not provide a name, MySql will generate one

mysql> CREATE TABLE test_a (
    ->     aaa int NOT NULL PRIMARY KEY,
    ->     bbb int,
    ->     ccc int
    -> );

mysql> ALTER TABLE test_a ADD INDEX (bbb);                -- no name specified

mysql> ALTER TABLE test_a ADD INDEX my_index_name (ccc);  -- name specified

mysql> SHOW CREATE TABLE test_a;
+--------+----------------------------------
| Table  | Create Table                     
+--------+----------------------------------
| test_a | CREATE TABLE `test_a` (
              `aaa` int(11) NOT NULL,
              `bbb` int(11) DEFAULT NULL,
              `ccc` int(11) DEFAULT NULL,
              PRIMARY KEY (`aaa`),
              KEY `bbb` (`bbb`),                          -- name generated automatically
              KEY `my_index_name` (`ccc`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
+--------+----------------------------------

 

Edited by Barand

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.