…, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN…, ‘ ‘, ‘LEFT OUTER JOIN ‘, K.REFERENCED_TABLE_SCHEMA, ‘.’, K.REFERENCED_TABLE_NAME, ‘ AS ‘, K.REFERENCED_TABLE_NAME, ‘ ‘, ‘ ON (‘, GROUP_CONCAT(CONCAT(K.CONSTRAINT_NAME…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… the time is each index chosen? SELECT iu.query_id, CONCAT_WS(‘.’, iu.db, iu.tbl, iu.idx) AS idx, variations…_cnt * 100 AS pct FROM index_usage AS iu INNER JOIN ( SELECT query_id, db, tbl, SUM(cnt) AS total_cnt… lots of them: mysql> SELECT CONCAT_WS(‘.’, db, tbl, idx) AS idx, -> GROUP_CONCAT(alt_idx) AS alternatives, -> GROUP_CONCAT(DISTINCT query_id) AS…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… the amazing MySQL manual, especially the option and variable …commanual general_log_fileblogpercona.commanual group_concat_max_lenblogpercona.commanual Handler_commitblogpercona….insert_idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_files_…
Post: Be careful when joining on CONCAT
October 16, 2007 By 10 Comments
… was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT(‘prefix-’, tb2… that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT(‘prefix-’, CAST…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…”,”b”)))/2*4000000000)),”;”) cmd from information_schema.table_statistics ts join information_schema.tables t on t.table_name=ts.table… more simple command to assign tables to the key caches: mysql> select concat(“CACHE INDEX “,table_schema,”.”,table_name,” IN “,table_schema… where table_schema=’test’ and table_name in (“a”,”b”); +——————————————————————————————-+ | concat(“CACHE INDEX “,table_schema,”.”,table_name,” IN “,table_schema,”_”,table…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 43 Comments
MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the…: old way: with group_concat: This should work faster, as we remove loop from PHP to MySQL server side. Also it can…: Sure, last example can be handled with one query with joins, but sometimes we need the temporary ids in clients code…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…*All aggregate functions supported, except GROUP_CONCAT and AVG(distinct). **non-…table_alias: o mview_join_condition: NULL mview_join_order: 999 *************************** 2. …<-- too high +------------------+ 1 row in set (0.68 sec) mysql> select sum(total_lines) from dashboard_customer…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… tables which were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop…_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz… do changes in test envinronment first. Note this query requres MySQL with Percona Extensions and user statistics running.
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…=mysql –mysql-host=localhost –mysql-table-engine=innodb –mysql-db=test –oltp-table-name=md_cache_test_small –oltp-table-size=1100000 –mysql-user=msandbox –mysql… take a look at the buffer pool contents. mysql [localhost] {msandbox} (information_schema) > select concat_ws(‘.’, t.schema, t.name, i.name… data_size_mb from innodb_sys_tables as t inner join innodb_sys_indexes as i using(table_id) inner…
Comment: Using sets to solve difficult decision problems – the subset sum problem
May 17, 2011 By 10 Comments
…: 9 Duplicates: 0 Warnings: 0 mysql> select sum_to_check_for, group_concat(a.val), group_concat(sum_to_check_for), sum(a.val) = sum_to_check_for matched from ex2 a join sum…) = sum_to_check_for ; +——————+———————+——————————–+———+ | sum_to_check_for | group_concat(a.val) | group_concat(sum_to_check_for) | matched | +——————+———————+——————————–+———+ | 0 | 0 | 0…
