… drawback, Batched Key Access (BKA) optimization was introduced. When BKA is being used then, after the selected rows are read from table1… as probe step. This join algorithm works best for highly selective queries, and obviously when the first operand used in the… read more about these variables here. The query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… query works in this case: mysql> explain (select * from utest where c1=5) union (select * from utest where c2=5) order by… by) optimally – in creates result set for the union fully and when only takes 10 rows from it: mysql> explain (select * from utest where c1=5 ) union (select * from utest where c2=5 ) limit 10…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… timestamp=1347398285; select * from sbtest where pad=’abc’; Rows_examined=0 same as Rows_sent meaning this query is optimized quite well…; select count(*) from sbtest group by k; This only sends 2 rows while scanning 10 million, while we can’t really optimize… would become “select * from sbtest” which would send all 10M rows and hence there is no ways to simply optimize it. This…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… single query ? Sure. Here is Way Number Three: mysql> select * from (select grp, count(*) cnt from dt where slack like “a%” group… implemented inside MySQL. Why am I looking on reporting performance optimization ? It is for ClickAider project which is growing rapidly and…%” group by grp with rollup \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dt type: ALL possible_keys: NULL key…
Post: How to obtain the "LES" (Last Executed Statement) from an Optimized Core Dump?
September 9, 2012 By 9 Comments
…issue” is seen when using optimized (release) binaries: debug symbols have been stripped out. In non-optimized binaries, the query would …3135 lex->duplicates, 3136 lex->ignore))) 3137 { 3138 res= handle_select(thd, lex, sel_result, OPTION_SETUP_TABLES_DONE); 3139 /* …
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… the table (no joking). Sometimes you would see 100 stories selected so one random of them will be displayed and similar… amount of rows are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return only… much more rows because it is optimized differently. One of typical examples here would be SELECT * FROM tbl WHERE id=5 executed…
Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush
February 27, 2013 By 3 Comments
… | Info | | 32 | Writing to net | select * from t where c > ‘%0%’ | | 12 | Waiting for table flush | select * from test.t where i… | Waiting for table flush | select * from test.t where i=11 | | 11 | Waiting for table flush | select * from test.t where i… problem of ANALYZE. Other commands like FLUSH TABLES, ALTER, RENAME, OPTIMIZE or REPAIR can cause threads to wait on “Waiting for…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name…,19,20) order by last_online desc limit 10; +—-+————-+——–+——-+—————+——+———+——+——-+—————————–+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… number of tables. I was wondering if 0 is automatic selection why do we have value of 62 being default in MySQL 5.5 which can produce very expensive plan selections ? Investigating this further I found the following explanation from Timour… from optimal plans do we get by using a greedy search. From the same discussion we can learn how automatic selection works…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
… with MyISAM storage engine used to show difference: MySQL Optimizer correctly knows Innodb tables is clustered by primary key …primary key order: mysql> explain select * from innodb order by id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pktest …
