…: 2 POSITION_IN_UNIQUE_CONSTRAINT: 2 REFERENCED_TABLE_SCHEMA: test REFERENCED_TABLE_NAME: Foo REFERENCED_COLUMN_NAME: B 2 rows …TABLE_NAME, ‘ ‘, ‘ ON (‘, GROUP_CONCAT(CONCAT(K.CONSTRAINT_NAME, ‘.’, K.COLUMN_NAME) ORDER BY K.ORDINAL_POSITION), ‘) = (‘, GROUP_CONCAT(CONCAT(K.REFERENCED_TABLE_NAME, …
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…% of the time in fact. The next variation is in row 2, the same query, which uses an index on post_status… add LIMIT 2 to the query, because there are lots of them: mysql> SELECT CONCAT_WS(‘.’, db, tbl, idx) AS idx, -> GROUP_CONCAT(alt…, -> GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt -> FROM index_alternatives -> GROUP BY db, tbl, idx -> HAVING COUNT(*) > 1 limit 2…
Comment: GROUP_CONCAT useful GROUP BY extension
April 29, 2009 By 49 Comments
…GROUP_CONCAT(id) AS CHAR) FROM `user` WHERE `nom` in(‘renaud’, ‘julie’, ‘lucy’); +—————————————-+ | @lid := CAST(GROUP_CONCAT(id) AS CHAR) | +—————————————-+ | 4,6,7 | +—————————————-+ 1 row…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…Y* N N** N*** Y *All aggregate functions supported, except GROUP_CONCAT and AVG(distinct). **non-deterministic functions like RAND(),NOW(), …: o mview_join_condition: NULL mview_join_order: 999 *************************** 2. row *************************** mview_table_id: 28 mview_id: 21 mview_table_…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ…_indexes.index_total_size / ( 1024 * 1024 ), 2) as index_size_mb from all…| staff | idx_fk_store_id | 0.02 | +————–+—————+—————————–+—————+ 22 rows in set (0.02 sec) Recovering…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
…_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),’G') data, concat(round(sum(index_length)/(1024*1024*1024),2),’G') idx, concat…)/(1024*1024*1024),2),’G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY engine…
