Category Archives: MySQL

Alter tables with MySQL replication

I couldn’t find anything particularly useful about how to alter MySQL table structure in a situation where replication was being performed so I pretty much had to try something out to see if it worked. What I found was that … Continue reading

Posted in MySQL, Programming | Leave a comment

TRUNCATE TABLE on MySQL InnoDB databases

Having come up against the extremely poor performance of using TRUNCATE instead of DELETE on MySQL InnoDB tables (see previous post MySQL Truncate slow performance problems) I thought I better come up with a solution that didn’t mean leaving a … Continue reading

Posted in MySQL, Programming | 2 Comments

Multiple SQL queries using MySQL and PHP

Something that I’ve had problems with using MySQL/PHP is the limitation of only being able to run one line of SQL at a time. Using something such as Microsoft SQL Server it’s possible to write multiple lines of SQL and … Continue reading

Posted in MySQL, PHP, Programming | 3 Comments

Optimising MySQL a query with packed keys

I’ve been learning more about MySQL lately and particularly optimising SQL queries on large tables. Large, in this case, being at the moment hundreds of thousands of rows but soon to be millions. One of the problems I’ve had is … Continue reading

Posted in MySQL, Other, Programming | Leave a comment

MySQL Truncate slow performance problems

I was having problems with a MySQL TRUNCATE taking a long time on a very large table (with foreign keys). I had thought that TRUNCATE ran more quickly than DELETE but, according to the MySQL manual, that isn’t the case … Continue reading

Posted in MySQL, Programming | 1 Comment

Implementing Iterator in PHP 5

There’s a good article on sharepoint about implementing Iterator in PHP 5: http://www.sitepoint.com/article/php5-standard-library/1. I’ve used it a couple of times now and it’s a great way to treat objects as collections. More info about the Standard PHP Library can be … Continue reading

Posted in MySQL, Programming | Leave a comment

MySQL load data infile and foreign key constraints

I’ve been up against a(nother) MySQL 4/PHP 5 ‘not doing all I want it to’ type problem. This time, it involved trying to do a bulk update on potentially tens of thousands of lines. Now, I don’t want to do … Continue reading

Posted in MySQL | 1 Comment