June 19, 2013

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…create_eventblogpercona.commanual Com_create_functionblogpercona.commanual Com_create_indexblogpercona.commanual Com_create_procedureblogpercona.commanual Com_create_serverblogpercona.commanual Com_create_tableblogpercona.commanual Com_create…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… InnoDB table that has never had a full-text index on it, and then I create one, the following warning is generated: mysql> alter table… indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER TABLE statement, but it’s not possible to CREATE more than one at a time. If you try it, this is what happens: mysql> alter table dir_test…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Converting Character Sets

March 17, 2009 By 22 Comments

…CREATE TABLE `t1`\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE…ALTER statement against the table in Approach #1: ALTER TABLE…

Filed Under: Insight for Developers Tagged With: , ,

Post: pt-online-schema-change and default values

June 21, 2012 By 1 Comment

…=sbtest Altering `sbtest`.`sbtest`… Creating new table… Created new table sbtest._sbtest_new OK. Altering new table… Altered `sbtest`.`_sbtest_new` OK. Creating triggers… Created triggers … SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`…

Filed Under: Insight for DBAs, MySQL

Comment: Concatenating MyISAM files

November 26, 2012 By 5 Comments

… ‘ALTER TABLE x ADD/DROP INDEX‘, which rewrites the whole .MYD. CREATE TABLE z LIKE x; — duplicate the structure of the original table. ALTER TABLE z DROP INDEX PRIMARY, DROP INDEX my_idx, ADD INDEX PRIMARY ( f1…

Filed Under: MySQL

Post: Data compression in InnoDB for text and blob fields

May 30, 2012 By 2 Comments

…bytes of variable length columns (BLOB, VARCHAR, TEXT) in the index record, and the remainder stored in overflow pages.  COMPACT …existing tables with an ALTER TABLE statement. As table compression is table specific, ROW_FORMAT and KEY_BLOCK_SIZE directives are passed via CREATE TABLE or ALTER TABLE …

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

… difference of behavior of ANALYZE TABLE for MyISAM vs Innodb. I used the following simple table for tests: CREATE TABLE `antest` ( `i` int(10… index prefix is unique (245900 is estimate for the row count in the table) It is worth to note if you do ALTER TABLE Innodb, same as MyISAM will internally run analyze as soon as table is rebuilt and values will be more sensible: mysql> alter table…

Filed Under: Insight for DBAs Tagged With: ,

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

… the corresponding number of concurrency tickets used for each: mysql> CREATE TABLE test_table ( -> id int -> ) ENGINE=InnoDB; — 0 Tickets Used Query OK… sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table ADD INDEX (id); — 5 Tickets Used Query OK, 3 rows affected…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , ,

Comment: MySQL Server Memory Usage

September 1, 2006 By 21 Comments

…’2416986544′ ‘Com_admin_commands’, ’76′ ‘Com_alter_db’, ’0′ ‘Com_alter_table‘, ’7′ ‘Com_analyze’, ’0′ ‘Com_backup_table‘, ’0′ ‘Com_begin’, ’0′ ‘…’, ’0′ ‘Com_commit’, ’9558970′ ‘Com_create_db’, ’0′ ‘Com_create_function’, ’0′ ‘Com_create_index‘, ’0′ ‘Com_create_table‘, ’31′ ‘Com_dealloc_sql’, ’0′ ‘…

Filed Under: Insight for DBAs Tagged With: , ,

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…tables locked by transactions. Innodb does not lock tables for normal operation so number of tables locked normally stays 0, unless it is ALTER TABLE…index. You can also see number of hash index lookups and number of non-hash index lookups which is indication of hash index…

Filed Under: Insight for DBAs Tagged With:
« Older Posts
Newer Posts »