…: GROUP_CONCAT is rather slower than a regular while( mysql_fetch_array() ) PHP loop. The example here is just for simplicity. But GROUP_CONCAT is… even try something like this: SELECT `p`.`name`, `p`.`description`, group_concat( concat( `pf`.`description`, ‘: ‘, `pf`.`filename` ) SEPARATOR ‘;’ ) FROM `pages` `p` LEFT JOIN…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),’G') data, concat(round…_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length…
Comment: GROUP_CONCAT useful GROUP BY extension
April 29, 2009 By 49 Comments
…’),(9, ‘aurelien’); mysql> SELECT @lid := CAST(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 in set (0.00 sec) mysql> SELECT @lid; +——-+ | @lid | +——-+ | 4…
Comment: GROUP_CONCAT useful GROUP BY extension
September 7, 2006 By 49 Comments
[...] GROUP_CONCAT useful GROUP BY extension: MySQL has useful extension to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group. [...]
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… below output is at the end) mysql> source list.sql +————–+————+—————–+—————–+————-+———————–+————————+ | table_schema | table_name…’, ‘NON UNIQUE’) as constraint_type, convert(group_concat(column_name ORDER BY seq_in_index …
Comment: MySQL and predictive option handling - beware
October 30, 2012 By 2 Comments
… often go unnoticed: [mysqld] group=mysql Of course, that means set group-concat-max-len to a value of “mysql“. Since “mysql” is an invalid number… the variable is 4 we end up with this: option ‘group_concat_max_len’: unsigned value 0 adjusted to 4
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… at tables which were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop…_schema and ts.table_name=s.table_name)>0) d group by table_schema,table_name; +———————————————————–+ | stmt | +———————————————————–+ | alter table board.country…
Comment: GROUP_CONCAT useful GROUP BY extension
May 13, 2009 By 49 Comments
When I am using in GROUP_CONCAT, It will list all the values associated wit the parent … ordinary SQL statement. Anybody know how to do this in MySQL qeury? tblmovies MovieId MovieName 1 The Take 2 Fatal Contact… 6 When I was selecting the Movie whith Id 1 GROUP_CONCAT will result Horror,Documentary, Drama ; But I want to get…
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…_index as p using(index_id) where t.schema=’test’ group by i.index_id \G INDEX_NAME DATA_SIZE_MB…
Comment: GROUP_CONCAT useful GROUP BY extension
May 2, 2012 By 49 Comments
You can chanage separator as follows: mysql> SELECT *, -> GROUP_CONCAT(DISTINCT test_score -> ORDER BY test_score DESC SEPARATOR ‘ |’) -> FROM table_name
