…, 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: 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: 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: 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: 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: 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: 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: MySQL Performance - eliminating ORDER BY function
November 18, 2007 By 6 Comments
[...] about MySQL performance optimization. He and his partners of Percona write about topics like eliminating ORDER BY function or Be careful when joining on CONCAT. Peter also held presentations at all Mysql Conferences. In [...]
Comment: Be careful when joining on CONCAT
November 18, 2007 By 10 Comments
[...] He and his partners of Percona write about topics like eliminating ORDER BY function or Be careful when joining on CONCAT. Peter also held presentations at all Mysql Conferences. In 2007, for example he talked about [...]
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…
