Efficiently Loading Paginated Results From MySQL with Just One Query Per Page

There are many situations in which pagination of query results is very useful, especially for performance optimization. ┬áIn most of these kinds of situations, the paginating of results requires you to determine the total number of results, so the application knows the number of pages available. The most common way to do this, is to… Continue reading Efficiently Loading Paginated Results From MySQL with Just One Query Per Page

Bash Shell Script to Convert All MySQL Tables to use the Innodb Engine

The following, when run in the Bash shell, will convert the engine for all of your MySQL tables to InnoDB. mysql -B -N -e “SHOW TABLES” -u –password= | while read table; \ do \ echo “+ Converting Table $table”; \ mysql -B -N -e “alter table $table engine=innodb” -u –password= ; \ done; Often,… Continue reading Bash Shell Script to Convert All MySQL Tables to use the Innodb Engine

Locating Duplicate Entries In A MySQL Table

If you don’t have unique indexes on your table, it is likely that you will occasionally have entries that are duplicated. This can often happen because of a software bug or possibly a user error. Some applications even choose not to have unique indexes for performance reasons; though this happens at the cost of data… Continue reading Locating Duplicate Entries In A MySQL Table

Eliminating Duplicate Records From MySQL Tables

Anyone who works with database driven development to any extent, will occasionally run into a situation where duplicate information is added to their database. I have personally run into such a problem on many occasions since I started working with database driven software. Being able to quickly undo data corruption is extremely important in production… Continue reading Eliminating Duplicate Records From MySQL Tables