On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still… about which query patterns the optimizer recognizes, and which are left to the developer to refactor manually. There could also be… upcoming Percona Training classes in North America and Europe. Join Percona and the MySQL community at our events. The next one is…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
… KEY (`id`)) —————————————————————————————————— $mysql=mysql_connect(’127.0.0.1′,’root’,”); mysql_select_db(‘test’,$mysql); function display_menu($parent, $level) { $result = mysql_query(“SELECT a.id, a.label, a.link, Deriv1.Count FROM `menu` a LEFT OUTER JOIN…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…these various tests. Clockwise from the top left in the next graph, we have “no cache”,…-requests=0 –percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-table-engine=innodb \ –oltp-…_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = …
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… easily solved by using the pager command: mysql> pager more PAGER set to ‘more’ mysql> select title from sakila.film; +—————————–+ | title | +—————————–+ | ACADEMY… say you have the following query: mysql> select count(*) from film left join film_category using(film_id) left join category using(category_id) where name=’Music’; and let’s say you want to change the left joins to inner joins and…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56 from var55 left join var56…
Post: Is there a performance difference between JOIN and WHERE?
April 14, 2010 By 30 Comments
… performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there’s no difference… inside MySQL and will have the same execution plan. SELECT * FROM A, B WHERE A.ID = B.ID; SELECT * FROM A JOIN… from “comma joins” because a) the ANSI join syntax is more expressive and you’re going to use it anyway for LEFT JOIN, and…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… MySQL will perform the join even if you use LEFT JOIN so it is not needed which slows down things considerably. In similar way MySQL… in set (0.00 sec) mysql> select count(*) from fact where i select count(*) from fact left join dim on val=id where i select i,pad from fact left join dim on val=id where…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… that information, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT….TABLE_SCHEMA, ‘.’, K.TABLE_NAME, ‘ AS ‘, K.CONSTRAINT_NAME, ‘ ‘, ‘LEFT OUTER JOIN ‘, K.REFERENCED_TABLE_SCHEMA, ‘.’, K.REFERENCED_TABLE_NAME, ‘ AS ‘, K… AN ORPHAN mysql> SELECT Bar_ibfk_1.ID AS `test.Bar.ID` FROM test.Bar AS Bar_ibfk_1 LEFT OUTER JOIN test…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…, all_indexes.index_name as index_name from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where… droppable_indexes view to use that information: mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select all… / ( 1024 * 1024 ), 2) as index_size_mb from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… are for MySQL 5.1.18 using MyISAM and Innodb tables. This time unlike other benchmarks I decided to do Join not… DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin t2 on… key lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin t2 on…
