…one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP,…: cast_info type: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows:…-like commands or optimizer_switch flags- we can disable ICP, but not “using index”. So, I wouldn…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
… | A | | B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row …C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… should be fine. Q: What are the key concerns upgrading MySQL 4.1 to MySQL 5.5? This is jumping over 2 Major… former execution plan either by updating statistics (ANALYZE TABLE) or disabling some of the optimization/statistics related features in new version… incompatible with MySQL. Our focus on compatibility is indeed so high there are number of great features which are disabled by default…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… last few years I’ve been suggesting more people to disable Query Cache than to enable it. It can cause contention… is appealing to improve performance for MySQL Applications ? Make it Lockless Can we re-implement MySQL Query Cache so it is lockless… applications. Many applications in MySQL have very large number of queries doing updates through primary key and use primary key for selects. I…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… 62742394, 62742398, 62742402, 62743720, 62743962, 62744142, 62744144, 62744146) With secondary key defined on (group_id,deleted) This pattern of medium size…,mysql_execute_command,mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_conne ction,start_thread,clone P.S Disabling…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… 6M (Hash Join and Key-ordered Scan disabled) You can see that the lowest query time is for MySQL 5.6 which takes 0.16s less as compared to MySQL… MariaDB with Hash Join and Key-ordered Scan enabled, and MariaDB with Hash Join and Key-ordered Scan disabled, and the only difference…
Post: High availability for MySQL on Amazon EC2 – Part 2 - Setting up the initial instances
June 29, 2010 By 9 Comments
…-1cdf3775 pending yves-key 0 m1.small 2010-06-18T20:11:14+0000 us-east-1c aki-aca44cc5 monitoring-disabled instance-store INSTANCE i-a03a21cb ami-1cdf3775 pending yves-key 1 m1.small 2010-06… by doing: root@hamysql:~# apt-get install mysql-server-5.1 The package install MySQL has an automatic startup script controlled by…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… by changes done in MySQL 4.1 As Monty explained us in MySQL 4.1 the change to key cache locking was done… second confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0). Results for MyISAM with key_buffer_size=0… have more queries per second for 4, 8, 16 with disabled key_cache. The results for 2 threads is however quite unexpected…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… we came back, we forgot that we had to re-disable foreign key checking since we were in a new session. We…) Oops, we forgot to disable foreign key checking. No problem, right? Not exactly…. (root@localhost) [oops4]> set foreign_key_checks=0; Query OK… I think there’s something of an inconsistency present when MySQL won’t allow us to change an FK-referenced column…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… that had single-column indexes on them, MySQL could sometimes make use of the multiple … adjust the optimizer_switch configuration setting and disable index_merge_intersection. It’s a dynamically-…SIMPLE table: users type: ref possible_keys: user_type key: user_type key_len: 2 ref: const rows: …
