…MySQL command-line client that wasn’t started with -A, and it’s trying to get a list of tables and…TABLES WITH READ LOCK will complete quickly. In some cases, it is unavoidable. This includes backing up a mixture of MyISAM and InnoDB…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… key for some queries and use key K as covering index for lookup in other direction. For Innodb Table id2 is not needed pas part of second key as PRIMARY key is appended to it internally anyway. For MyISAM table you… this case is how MySQL will execute the join. If it will fist lookup Table2 using some other index and when go to…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… similar tables with where clause and joins. It is important for tables which is being read to be Innodb – even if writes are done in MyISAM table. So why was this done, being pretty bad for MySQL Performance and concurrency ? The reason is – replication. In MySQL before 5…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES FROM for tables involved and check… old MySQL Version to get slower plan instead of new one getting faster. Check stats sampling settings such as innodb-stats-method and myisam…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) … state (state) ) ENGINE=MyISAM; 3) Table with INT: CREATE TABLE cities_join ( id int(10)…Table Scan operation difference often would be larger. It is also interesting to note performance of Innodb tables…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…InnoDB tables — don’t use GUIDs. Which brings me to my next point: Use InnoDB Assuming that you will use the stock MySQL…MyISAM tables and taking downtime, I would not use MyISAM for anything but read-only tables when things get big. And…
Post: MySQL 5.6.7-RC in tpcc-mysql benchmark
October 8, 2012 By 22 Comments
…innodb_buffer_pool_instances: 1 and 8, and innodb_log_file_size: 2x4GB and 2x8GB. Results The first result is 2x4GB innodb…myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover user=root skip-grant-tables…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…table design and understanding inner works of MySQL. If you design your data wisely considering what MySQL can do and… – quite possible scenario for MyISAM tables. Now if we take… also clustered keys in Innodb which combine index access … table scan may be faster. Avoid joins to large tables Joining of…
Comment: Using CHAR keys for joins, how much is the overhead ?
June 19, 2007 By 18 Comments
…”normal” join with short columns containing integers as a strings and LATIN1…MySQL may want to profile it in more details. Changing column types from INT to BIGINT slowed down join speed for MyISAM tables… join tests for Innodb table with utf8 encoding. This caused some degradation slowing down join…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… noatime and nodiratime options if you’re using innodb_file_per_table and a lot of tables though …one of our MySQL Presentations. Application tuning for Innodb Especially when coming from MyISAM background there …in joins), large unpacked indexes (try to be easy on indexes). With these basic innodb …
