…optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=32M optimizer_switch=’batched_key_access…In this test we can clearly see that Hash Join gives a lot of reduction in query time. The reduction in query…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
…query actually accessed we can guess (what is important automatically !) there is potential for optimizing this query. For example: SELECT…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…` (`country_id`,`state_id`,`city`) ) In this benchmark we used only read (SELECT) queries with different typical data access patterns: primary key single row… a big room for optimization. READ_PK_RANGE Query: SELECT min(dob) FROM $tableName WHERE id between %d and %d Access by range of…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…_id: 12F03 use sbtest; SET timestamp=1347397784; select * from sbtest where pad=’abc’; The query in this case has sent zero rows (as… timestamp=1347398285; select * from sbtest where pad=’abc’; Rows_examined=0 same as Rows_sent meaning this query is optimized quite well. Note you may be thinking in this case there is no database access happening at all…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… 0.000009 | | init | 0.000014 | | optimizing | 0.000002 | | statistics | 0.000005 | |…in the Table Cache have to be invalidated, and any query that accesses…query | 0.000002 | | cleaning up | 0.000002 | +———————-+———–+ 17 rows in set (0.00 sec) As you can see that the SELECT query…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… in the case where I have two distinct access patterns: single row primary-key lookups and SELECT…the applicable queries. Imagine those 900-row SELECT statements were actually 10,000 row selects, this would…of the PK lookups). In other words, if you’re optimizing for throughput in this scenario, you…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…remember avoiding accessing database is the best way you can optimize database access. This applies …query would not need to analyze much more rows because it is optimized differently. One of typical examples here would be SELECT…seen people trying to join all queries in single UNION (with padding to …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… selectivity might show benefit from using indexes. In fact even MySQL optimizer currently does not take it into account. For In memory workload index accesses… to be extra careful working with large data sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
…” which was added in MySQL 4.1 EXPLAIN EXTENDED can show you what the MySQL optimizer does to your query. You might not… query rewrites that MySQL makes when accessing views which use the MERGE algorithm. For example: mysql> create view v1 as select * from j1; Query… that the SELECT statement used in the view has been modified to include the WHERE clause that I used when accessing the view…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… “system” access type it is effectively removed from join execution (replaced with constants) and so ORDER BY can be optimized even if…) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity or just better estimates… end up in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select * from test…
