-
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…
-
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,…
-
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…
-
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…