Category Archives: Database

Do you need to call PDOStatement::closeCursor when you’re done with the statement?

Posted on by

The answer is no, so long as you’re not preparing to execute the statement again. I figured this out by looking at the code for PDOStatement::closeCursor and the MySQL implementation. Seems to me that all the freeing necessary is done in the destructor so if you’re not planning to use the statement again it seems to me that you can safely omit the call to PDOStatement::closeCursor(). On the other hand if you are going to reuse the statement calling closeCursor seems like it’s a pretty important thing to do. It would be nice if PDOStatement::fetchAll() called closeCursor() for us, but I don’t think it does.

A great MySQL session

Posted on by

I found this great MySQL session over here. It has a clever approach for creating a big table, and shows how to invoke shell commands from the `mysql` client.

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL autocommit=0;

-- Create an uncompressed table with a million or two rows.
CREATE TABLE big_table AS SELECT * FROM information_schema.columns;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
COMMIT;
ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEY auto_increment;

SHOW CREATE TABLE big_table\G

select count(id) from big_table;

-- Check how much space is needed for the uncompressed table.
\! ls -l data/test/big_table.ibd

CREATE TABLE key_block_size_4 LIKE big_table;
ALTER TABLE key_block_size_4 key_block_size=4 row_format=compressed;

INSERT INTO key_block_size_4 SELECT * FROM big_table;
commit;

-- Check how much space is needed for a compressed table
-- with particular compression settings.
\! ls -l data/test/key_block_size_4.ibd

MySQL admin with Ian Gilfillan

Posted on by

I was doing some reading today and I came upon an old series of articles over on www.databasejournal.com by a dude called Ian Gilfillan:

For the ‘type’ in SQL ‘EXPLAIN’ Ian says: from best to worst the types are: system, const, eq_ref, ref, range, index, all.
It seems this Ian Gilfillan fellow has been rather prolific.