mysql tables: primary and unique key

Discussions about development of VICIDIAL and astGUIclient

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

mysql tables: primary and unique key

Postby gschaller » Fri Feb 02, 2007 4:56 am

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.
gschaller
 
Posts: 99
Joined: Thu Jun 29, 2006 1:59 pm

Postby mflorell » Fri Feb 02, 2007 6:00 am

It is basically ignored, I included it for compatibility with older veriosn of MySQL, but it shouldn't really be necessary at this point.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby gschaller » Fri Feb 02, 2007 1:04 pm

Ok so I will delete the extra unique keys to save disk space and performance.
gschaller
 
Posts: 99
Joined: Thu Jun 29, 2006 1:59 pm

Postby mflorell » Sat Feb 03, 2007 11:49 am

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.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby gschaller » Sat Feb 03, 2007 6:23 pm

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.
gschaller
 
Posts: 99
Joined: Thu Jun 29, 2006 1:59 pm

Postby mflorell » Sat Feb 03, 2007 6:52 pm

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?
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby gschaller » Sat Feb 03, 2007 7:23 pm

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
 
Posts: 99
Joined: Thu Jun 29, 2006 1:59 pm

Postby gschaller » Sun Feb 04, 2007 5:14 am

mflorell wrote: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
gschaller
 
Posts: 99
Joined: Thu Jun 29, 2006 1:59 pm

Postby mflorell » Sun Feb 04, 2007 8:54 am

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
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby mflorell » Sun Feb 04, 2007 9:07 am

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;
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Postby gschaller » Sun Feb 04, 2007 10:22 am

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.
gschaller
 
Posts: 99
Joined: Thu Jun 29, 2006 1:59 pm

Postby mflorell » Sun Feb 04, 2007 11:03 am

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.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida


Return to Development

Who is online

Users browsing this forum: No registered users and 33 guests