… patterns the optimizer recognizes, and which are left to the developer to refactor manually. There …count the movies per production year for each kind_id. It was more efficient in this case to force MySQL… classes in North America and Europe. Join Percona and the MySQL community at our events. The …
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…(‘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 (SELECT parent, COUNT(*) AS Count FROM `menu…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… tests. Clockwise from the top left in the next graph, we have “no …MySQL & Benchmark Configuration All benchmarks were conducted with the following: sysbench –num-threads=32 –test=tests/db/oltp.lua –oltp_tables_count…_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
… 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
…: 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… be scaled down compared to MySQL 5.5 default values. Such as performance_schema auto scales to count up to 445 tables…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
…) mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row 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…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…_NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_… to use that information: mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes … * 1024 ), 2) as index_size_mb from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this …query: select table_schema, table_name, page_type, count(*) from information_schema.innodb_buffer_pool_content …the top tables on the active server and left-join those against the tables on the passive server…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… output as returned by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s… output as returned by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s… are joining tables with no indexes on the join condition (Full Join). The best performance with Hash Join can be achieved when the left table…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… you get number of comments left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in… will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt from user_counts uc, user_counts…
