…, 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 16 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: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES from information_schema.tables t join information….NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR… droppable_indexes view to use that information: mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select all…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 49 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 20 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: Analyzing Slow Query Table in MySQL 5.6
February 18, 2013 By 1 Comment
… here’s mine: cat <<'GO' | mysql –raw –skip-column-names –quick –silent –no-auto-rehash –compress $* SELECT CONCAT( ‘# Time: ‘, DATE_FORMAT(start…,Set option,Fetch,Daemon,Error’), CONCAT(‘# administrator command: ‘, sql_text), sql_text), ‘;’ ) AS `# slow-log` FROM `mysql`.`slow_log`; GO echo “#” I… manipulating query logs and running reports on them? Register to join me February 25-28, 9AM-11AM Pacific Time, for my…
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.
