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 MySQL was remarkably clever at replicating changes through from one database to another. I don’t …
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 table to clear for an hour. The solution is to use a combination of SHOW …
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 run it all in a single database call. Until stored procedures (in MySQL 5) are …
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 that MySQL sometimes decides not to use an index even when a handy one seems …
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 with InnoDB tables. In this case there is no difference between TRUNCATE and DELETE and …
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 found here: Standard PHP Library with a link to download a windows chm help file …
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 an INSERT for each line and I’ve already written some classes to handle outputting data …