June 19, 2013

Post: Best kept MySQLDump Secret

March 23, 2012 By 13 Comments

…dumptest > dump.sql SESSION1: (before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row affected… — – Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_…

Filed Under: Insight for DBAs, MySQL

Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!

December 9, 2010 By 18 Comments

…) mysql> alter table a drop key c; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table | Op… | +——–+———-+———-+——————————————————————-+ 2 rows in set (4 min 5.52 sec) mysql> alter table a add key(c); Query OK, 0 rows affected (5 min 51.83…

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

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

… are flat files, that should be faster, especially with the keys disabled. At that point, I remembered, from a previous disaster… (0.00 sec) mysql> truncate table test_concat_part; Query OK, 0 rows affected (0.01 sec) mysql> alter table test_concat add data varchar(10); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table test…

Filed Under: MySQL

Post: Find unused indexes

June 30, 2012 By Leave a Comment

…ALTER TABLE `tpcc`.`order_line` DROP KEY `fkey_order_line_2`; — type:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE…

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

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… now; then add the rest later, reloading the data if needed. Aggregate Aggregation is absolutely key for most…doesn’t save you from the downtime caused by ALTER TABLE — a typical reason to think about master-master … work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier,…

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

Post: MySQL Server Memory Usage

May 17, 2006 By 21 Comments

…add the size of Operation System cache you would like MySQL to use for your table. Take this number add…done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands. For …some queries. There can be mutiple key caches. Multiple key caches can be created to …

Filed Under: Insight for DBAs Tagged With: , ,

Post: Full table scan vs full index scan performance

November 23, 2012 By 15 Comments

…take the employees database, and slightly modify the employees tables: mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB; And…1. row *************************** id: 1 select_type: SIMPLE table: employees type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: …

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

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

… ? mysql> alter table idxitest drop key a,add key(a,b); Query OK, 0 rows affected (24.84 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select….02 sec) mysql> explain select count(*) from idxitest where a=5 and b=5; +—-+————-+———-+——+—————+——+———+————-+——–+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref…

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

Post: MySQL EXPLAIN limits and errors.

July 24, 2006 By 6 Comments

…MySQL has triggers which can make updates very different. Also there is no way to tell you how ALTER table would be executed – will in add…

Filed Under: Insight for Developers Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE … 5.6 and running ALTER TABLE foo ENGINE=InnoDB. In theory, …add your first FULLTEXT KEY to an InnoDB table, be prepared for a table…

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