… me briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means… on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched_key_access…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… query executed on the InnoDB table: SELECT non_key_column FROM…Thereby, converting Random access to one or more sequential access. There is one…MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer…
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
… to focus only on Server Side optimization – the Client Side Optimization is also quite important in …MySQL table for larger ones you can log only small portion of them. I usually keep one table… for. Large portion probably comes from memcache accesses which are not instrumented for this application. …
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… Just remember avoiding accessing database is the best way you can optimize database access. This applies to anything… example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return only one row while…rather than simply adjusted – for example summary table which holds number of links per …
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… in set (0.00 sec) However if first table has “const” or “system” access type it is effectively removed from join execution (replaced with constants) and so ORDER BY can be optimized even if it is done by second table: mysql> explain select test.i from test…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
…they are stopped from accessing any table. Here is …if that is a MySQL table, the mutex remains …TABLE to minimize the effect, such as: TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table; TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands. For …Innodb Table Cache. Innodb has its own table cache in which meta data about each table accessed from…of tables. It also means user having CREATE TABLE privilege should be able to run MySQL …
Post: Logging MySQL queries from the client instead of the server
February 2, 2011 By 2 Comments
… to examine query execution on your MySQL server. Queries are logged with timing …is no access to the database server. I’ve seen this when access was forbidden by… the task you’re trying to optimize or analyze. You might also be… you can log queries to a database table and then use SQL to analyze …
Post: Learning about MySQL Table Fragmentation
May 1, 2008 By 19 Comments
… get Innodb table fetched in memory as fast as possible to get good in memory access performance….sequential read rate or you would see MySQL becoming CPU bound if IO subsystem is …OPTIMIZE TABLE tbl – this command recreates the table by writing the new .ibd file (if you’re using innodb_file_per_table…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… indexes. In fact even MySQL optimizer currently does not take it into account. For In memory workload index accesses might be faster even… Innodb which combine index access with data access, saving you IO for completely disk bound workloads. There are certain optimizations in works which… to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster…
