Tag Archives: mysql

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.

watch catting together HTML head/foot and MySQL information_schema.processlist

Posted on by

This came up back on August 9th 2020 in #lobsters on freenode. They were doing a system upgrade and providing a report by using `watch` to `cat` together a HTML header and footer with `mysql -e ‘select * from information_schema.processlist’` to provide a status report. Thought that was a neat hack.