…am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just…_join_sizeblogpercona.commanual sql_modeblogpercona.commanual sql_notesblogpercona.commanual sql_quote_show_createblogpercona.commanual sql_safe_updatesblogpercona.commanual sql_select_…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count(pad) from large…reduce the gap but I doubt it will be closed. Joins Joins are used to compose the complex object which was …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…(0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(val) from ex1; …etc select * from compressed join search_set using (val) where compressed.cnt >= search_set.cnt; mysql> select * from compressed join …
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… 14184 join_init_cache(THD *thd,JOIN_TAB *tables,uint table_count) 14185 { … …join_buffer_size = 1 << 30; # 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as x2 join…join. If you execute this and nothing bad happens, you might be seeing this bug: http://bugs.mysql…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…=a.k; mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select_type | table… case we actually join 2 tables but because the access type to the tables is “const” MySQL does not count it as access…_table_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only sends 2 rows…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… were sold yesterday. SELECT COUNT(1) FROM tags t JOIN products p ON p.prd_id = t.tag_prd_id JOIN items_ordered i… those not matching the date range. A very inefficient approach! MySQL was unable to optimize those simple conditions to match both… rewritten query: EXPLAIN SELECT COUNT(1) FROM tags t JOIN products p ON p.prd_id = t.tag_prd_id JOIN items_ordered i…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…atomically, not just for new connections as in MySQL. This is very helpful for measurement as … F00 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: ….000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto_inc; Note that logging all queries…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i….as COLUMN_NAMES from information_schema.tables t join information_schema.statistics i using (table_schema,…of our SELECT. I can now select my droppable (unused) indexes from this view: mysql> select * from droppable_indexes; …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The other thing you need to consider is string comparison in MySQL… is used to join the two tables? A: The question in this case is how MySQL will execute the join. If it will… competition_id, nothing more. Would doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime…on-disk temporary storage for hash joins and other background operations. This will… parse time). $ echo “select count(*) from ontime_fact;” | ./run_query Array ( [count(*)] => 135125787 ) 1 rows returned…
