mysql tables: primary and unique key

[gschaller]

I wonder about the database tables. Always there is a primary key, but also a unique key on the same column. A primary key is unique, isn't it? So please tell me the need for the extra unique key.

[mflorell]

It is basically ignored, I included it for compatibility with older veriosn of MySQL, but it shouldn't really be necessary at this point.

[gschaller]

Ok so I will delete the extra unique keys to save disk space and performance.

[mflorell]

Just because UNIQUE is listed does not mean that an additonal index is being created, there is still only one index(the PRIMARY index), so there is nothing for you to delete.

[gschaller]

Definitly there must be a difference. For example my table vicidial_list (some more than 500000 entries): With the extra Unique on the column lead_id the space used by all index is more than 34 MB, without the extra unique on lead_id it is less than 30 MB. Just testet with phpmyadmin on MySQL 4.1.11.

[mflorell]

Can you detail the exact steps you use to convert from Primary UNIQUE key to only PRIMARY key and post "describe table..." before and afterward?

Did you do an "optimize table..." before doing this?

[gschaller]

A primary key is a unique one, but a primary key is a special unique key. Having lead_id as primary key and an extra unique key means to have two keys, but only one is/can be used ...
Before adding the extra Unique on lead_id:

Code: Select all

mysql> describe vicidial_list;
+-------------------------+------------------------------------------------------------------+------+-----+-------------------+----------------+
| Field                   | Type                                                             | Null | Key | Default           | Extra          |
+-------------------------+------------------------------------------------------------------+------+-----+-------------------+----------------+
| lead_id                 | int(9) unsigned                                                  |      | PRI | NULL              | auto_increment |


After "ALTER TABLE `vicidial_list` ADD UNIQUE (`lead_id`)":

Code: Select all

mysql> describe vicidial_list;
+-------------------------+------------------------------------------------------------------+------+-----+-------------------+----------------+
| Field                   | Type                                                             | Null | Key | Default           | Extra          |
+-------------------------+------------------------------------------------------------------+------+-----+-------------------+----------------+
| lead_id                 | int(9) unsigned                                                  |      | PRI | NULL              | auto_increment |


The more interesting point (having primary and unique on lead_id):

Code: Select all

mysql> show keys from vicidial_list;
+---------------+------------+-------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name                | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+-------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+
| vicidial_list |          0 | PRIMARY                 |            1 | lead_id                 | A         |      537374 |     NULL | NULL   |      | BTREE      |         |
| vicidial_list |          0 | lead_id                 |            1 | lead_id                 | A         |      537374 |     NULL | NULL   |      | BTREE      |         |


[gschaller]

Can you detail the exact steps you use to convert from Primary UNIQUE key to only PRIMARY key ...

Sorry I forgot the sql command for this:

ALTER TABLE vicidial_list DROP INDEX lead_id

[mflorell]

Thank you very much!

I was wrong, it's been many years since I had even thought about MySQL keys, and removing the extra unique keys from these tables should give a performance boost.

I will make the changes for the next release.

Thanks again!

[mflorell]

Here's the full list of changes for this issue that I have comitted to SVN:

ALTER TABLE vicidial_list DROP INDEX lead_id;
ALTER TABLE recording_log DROP INDEX recording_id;
ALTER TABLE call_log DROP INDEX uniqueid;
ALTER TABLE park_log DROP INDEX uniqueid;
ALTER TABLE vicidial_manager DROP INDEX man_id;
ALTER TABLE vicidial_hopper DROP INDEX hopper_id;
ALTER TABLE vicidial_live_agents DROP INDEX live_agent_id;
ALTER TABLE vicidial_auto_calls DROP INDEX auto_call_id;
ALTER TABLE vicidial_log DROP INDEX uniqueid;
ALTER TABLE vicidial_closer_log DROP INDEX closecallid;
ALTER TABLE vicidial_xfer_log DROP INDEX xfercallid;
ALTER TABLE vicidial_users DROP INDEX user_id;
ALTER TABLE vicidial_user_log DROP INDEX user_log_id;
ALTER TABLE vicidial_campaigns DROP INDEX campaign_id;
ALTER TABLE vicidial_lists DROP INDEX list_id;
ALTER TABLE vicidial_statuses DROP INDEX status;
ALTER TABLE vicidial_inbound_groups DROP INDEX group_id;
ALTER TABLE vicidial_stations DROP INDEX agent_station;
ALTER TABLE vicidial_remote_agents DROP INDEX remote_agent_id;
ALTER TABLE vicidial_agent_log DROP INDEX agent_log_id;
ALTER TABLE vicidial_scripts DROP INDEX script_id;
ALTER TABLE vicidial_lead_recycle DROP INDEX recycle_id;

[gschaller]

No problem. I have to thank you for vicidial
If I have some time the next weeks I will analyse the most used queries and the used keys. Maybe we need a key anywhere for more performance. Also I will try to tune the MySQL settings for key and query cache.

[mflorell]

I'm sure there are places where we can optimize performance. I haven't really concentrated on performance much.

Just post or email me off-list when you have some suggestions.

Thanks again.