Page 1 of 1
remove/optimize redundant indices
Posted: Thu Nov 22, 2007 2:43 pm
by carteriii
I see that each table has declared both a PRIMARY KEY and a UNIQUE KEY on the Id column of each table. I believe this is unnecessarily redundant. Unless MySQL has an optimization to ignore one of these indices entirely, I believe this is only causing inserts and updates (though the Id's are never updated) to be slightly slower since each index must be maintained separately for every row in the table. For what it's worth, you can find a query to find all redundant queries here:
http://forge.mysql.com/snippets/view.php?id=45
I'd also like to point out that the Events table defines a key on the combination of Id and MonitorId. This is also redundant with the other keys that begin with Id. Purely as an optimization, though at the cost of some confusion, you could define the primary key to be on ('Id', 'MonitorId') and then eliminate both of the other single indices on ('Id'). This can be done because an index on more than one column can always be used to query only the first column of the index. By contrast, an index on ('Id', 'MonitorId') cannot be used to query for only the MonitorId.
Posted: Thu Dec 06, 2007 4:56 pm
by zoneminder
Thanks for these suggestions. As the database has evolved rather than been planned from day one I do sometimes find redundant things like this. Thanks for taking the time to post such a thorough report.
Posted: Thu Dec 06, 2007 5:03 pm
by zoneminder
Just as a query on your second point. If the Events table had it's primary key changed to Id, MonitorId instead of Id do you know how/if that would affect the auto increments?
Posted: Thu Dec 06, 2007 6:09 pm
by carteriii
I believe it "should" be fine, but I'm more of a SQL Server/Oracle, so I'll test with MySQL for you this weekend (if not before) and let you know.
Posted: Thu Dec 06, 2007 8:18 pm
by zoneminder
Thanks, that would be useful.
I deleted the additional indices this afternoon and it has (as I guess would be expected) reduced the relevant .MYI files by about 50% so a result there as it doesn't seem to be optimising one out.
In case anyone else wants to do it (with the proviso that I have only just done so there may be unforeseen consequences though I would not see a reason why) just go into mysql, select your zm database and type
Code: Select all
alter table Config drop index `UC_Name`;
alter table Controls drop index `UC_Id`;
alter table Devices drop index `UC_Id`;
alter table Events drop index `UC_Id`;
alter table Frames drop index `UC_Id`;
alter table Users drop index `UC_Id`;
alter table ZonePresets drop index `UC_Id`;
alter table Zones drop index `UC_Id`;
It may take a couple of minutes and ideally you might want to stop ZM first and then restart it later.
Posted: Thu Dec 06, 2007 10:40 pm
by carteriii
I just ran a quick test on the primary key, and while certainly not exhaustive, I think it confirms that a primary key on multiple columns, one of which is an auto_increment, should work just fine. The simple script I ran is below. Note that I was not able to put the primary key column definitions in quotes as is the convention in zm_create.sql. Maybe it's just my version of MySQL, but ('Id','MonitorId') failed to parse, but (Id, MonitorId) parsed just fine.
Code: Select all
DROP TABLE IF EXISTS `Events`;
CREATE TABLE `Events` (
`Id` int(10) unsigned NOT NULL auto_increment,
`MonitorId` int(10) unsigned NOT NULL default '0',
`Name` varchar(64) NOT NULL default '',
PRIMARY KEY (Id, MonitorId)
) DEFAULT CHARSET=latin1;
INSERT INTO Events (MonitorId, Name) VALUES (1, 'Should Insert Fine');
INSERT INTO Events (MonitorId, Name) VALUES (1, 'Second Event for Monitor One');
INSERT INTO Events (Name) VALUES ('Should Default Monitor to Zero');
INSERT INTO Events (Name) VALUES ('Second Event with Monitor Zero');
INSERT INTO Events (MonitorId, Name) VALUES (0, 'Third Event for Monitor Zero');
SELECT * FROM Events;
Posted: Sat Dec 08, 2007 6:34 pm
by zoneminder
Thanks. Mysql tends to use the funny backquotes rather than regular single quotes so maybe you just tried the wrong sort
