MySQL – Should you put an index on a Boolean field to help query performance?
I am often asked if it makes sense to place an index on a Boolean field in order to improve query performance. In general, because a boolean value can only…
Read moreMySQL – Disabling Binary Logging for INSERT and UPDATE
I have run into many situations where there would be a benefit to not replicate a large INSERT or UPDATE from the MASTER to SLAVE in order to not block…
Read moreMySQL – Constraints on Hierarchical Data in a Self-Referential Table
I recently had a client who was using a table structure that utilized a self-referential structure using an ID field and Parent_ID fields. After upgrading to Innodb, his DBA was…
Read moreMySQL – ERROR 1005: Can’t create table (errno: 150) – INNODB
If you have seen this error, don’t worry, you are not alone. This error is often accompanied by a message that a table or file could not be created, and…
Read moreMySQL Removing duplicate rows – Part II
Using ALTER IGNORE TABLE [TABLENAME] ADD UNIQUE INDEX `UNIQUE_INDEX` ([FIELDNAME]) to remove duplicate rows in a table is a fast an efficient process, however on large tables where the physical…
Read moreMySQL How to delete duplicate records and rows of data
The fastest and easiest way to delete duplicate records is my issuing a very simple command. alter ignore table [tablename] add unique index `unique_index` ([fieldname]) What this does is create…
Read more