6 October 2004, by Karl Bunyan
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. 
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 available (i.e. when it seems that the database engine is ready
for a live environment) I’ve put together the following static method ‘hack’ using PHP’s exec() function (assuming you’re using PHP 5’s object syntax - otherwise just paste the code into a regular function):
class MySQLInterface
{
public static function multipleDbQueries($sql)
{
$file = fopen(TEMP_CSV_LOCATION .
"temp_query.sql","a+");
fwrite($file,$sql);
fclose($file);
exec("mysql -u " . DB_USERNAME .
" --password=='" . DB_PASSWORD .
"' " . DB_NAME . " < " .
TEMP_CSV_LOCATION .
"temp_query.sql");
}
}
What this allows you to do is to pass in a SQL string and have it executed as if it was being run from the command line. This is especially useful if you need to disable foreign keys for some reason. e.g
$sql = "SET FOREIGN_KEY_CHECKS=0;
drop table oldTable;
SET FOREIGN_KEY_CHECKS=1;"
MySQLInterface::multipleDbQueries($sql)
Downsides of this are
- Requires command line access to mysql
- Liable to SQL injection
Since i’m working within an internal system I have control over both of these and the code seems to work particularly well.
October 6, 2004 at 4:41 pm
Filed in: PHP, MySQL, Programming
No comments
4 October 2004, by Karl Bunyan
The main thing to
watch out for with InnoDB tables is foreign key constraints which are
easily disabled.
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 CREATE and DROP. DROPping a
table is very quick indeed, so as long as you have the CREATE code to
hand then it’s a simple matter to empty a table. The main thing to
watch out for with InnoDB tables is foreign key constraints which are
easily disabled.
Some sample code to use this from within PHP is shown below
function truncateTable($tableName)
{
//Grab the code to create the table
$sql = "show create table " . $tableName;
$dataSet = DataHandler::loggedDbQuery($sql);
$result = $dataSet->fetchRow();
$createSQL = $result["Create Table"] . ";
SET FOREIGN_KEY_CHECKS=1;";
//Drop the table. We have to disable foreign key
//checks, which means running the whole thing
//from the command line
$sql = "SET FOREIGN_KEY_CHECKS=0;
drop table " . $tableName . ";".
$createSQL;
DataHandler::multipleDbQueries($sql);
}
This
is used in conjunction with a static method I’ve created to run a
standard (single) SQL query from within PHP called
DataHandler::loggedDbQuery (which works with PearDB, which is where the
fetchRow() method comes from) and a multi-line query function I have
developed and wrote about in Multiple SQL queries using MySQL and PHP and referred to as DataHandler::multipleDbQueries($sql).
October 4, 2004 at 2:36 pm
Filed in: MySQL, Programming
No comments
3 October 2004, by Karl Bunyan
MySQL sometimes decides not to use an index
even when a handy one seems to have been created for it. The root of
this appears to be that with B-tree indexes if there are a large
number of records with similar looking values
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 to have been created for it. The root of
this appears to be that with B-tree indexes if there are a large
number of records with similar looking values then the MySQL engine may
decide that it’s just as much effort using the index as to search the
whole table.
The answer appears to be adding PACK_KEYS = 1 to the end of a create
table, or running the SQL command ALTER TABLE MyTable PACK_KEYS = 1 once
the table has been created. In effect, this takes account of the
similarity of adjacent keys. In our case we have a large column of field
type bigint(21) where the starting digits of the index are timestamp
generated. So, at present, we end up with a few tens of thousand rows
all starting with 108xx. Enabling packed keys means not only that the
index is smaller as MySQL only needs to store the differences between
keys (plus an extra byte to keep track of where the similarity starts)
but also that the index is actually of some use i.e. doesn’t become a
large, flat structure.
One down side of using packed keys is that inserts are slower, but
given
that the system we are building is inserting each row once and then (in
theory) never touching it again that’s a small price to pay. The other
major drawback, however, is that packed keys only works on MyISAM
tables at present and not InnoDB. This actually isn’t much use to me as
the large inserts we occassionally have to do would end up with MyISAM
locking the table for perhaps an hour or more.
Pack keys
reference in the MySQL manual
October 3, 2004 at 10:33 am
Filed in: MySQL, Programming
No comments
1 October 2004, by Karl Bunyan
Another one of those 'I wish PHP 5 did this...' moments has occurred to me with class constants. The addition of constants is good but the problem is when it comes to subclassing.
Another one of those ‘I wish PHP 5 did this…’ moments has occurred to me with class constants. The addition of constants is good but the problem is when it comes to subclassing. The code on the PHP 5 site:
class Foo {
const constant = "constant";
}
echo “Foo::constant = ” . Foo::constant . “n”;
is fine. Of course, what you really want to have is a method to give you the constant in case you want to change the workings later:
class Foo {
const constant = "constant";
public function getConstant(){
return self::constant;
}
}
$foo = new Foo();
echo “$foo->constant = ” . $foo::getConstant() . “n”;
and this works too. The problem is that if you decide a subclass needs a different constant value, so we add
class Bar extends Foo {
const constant = "bar constant";
}
If we then call
$bar = new Bar();
$bar->getConstant();
then the value returned is “get_constant” i.e. the value of the constant in the parent class. This is because Bar has no handler for getConstant() so it uses its parent. That’s fine, but now we’re in the parent context then Foo::constant is returned through the reference to self::. The way to get round this (that I have found) is to put a copy of the getConstant() method in each of the subclasses. This kind of defeats the purpose of inheritance in this case.
class Bar extends Foo {
const constant = "bar constant";
public function getConstant(){
return self::constant;
}
}
Now we call
$bar = new Bar();
$bar->getConstant();
and the correct value is returned. Of course, the other way round is not to use constants at all but to put the value in a private variable, but then what use are constants?
October 1, 2004 at 10:25 am
Filed in: PHP, Programming
No comments