… patterns the optimizer recognizes, and which are left to the developer to refactor manually. There …. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On the Dynamic Pivot, the straight join, … that it created a temporary table to count the movies per production year for each kind…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… of MySQL configuration parameters. For example, given that the front-end … tests. Clockwise from the top left in the next graph, we have “no …=tests/db/oltp.lua –oltp_tables_count=32 \ –oltp-table-size=10000000 –rand…_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size …
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… and remove JOIN for count(*) and do JOIN after limiting result set for retrieval queries. Lets look at following simple example with one dimension… 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: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… is greater than the join_buffer_size, then multiple hash tables would be created. For example if the left operand has “n” rows, and its size is three times the value of join_buffer_size, then… is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey WHERE s_nationkey…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
…| | ALLEY EVOLUTION | | ALONE TRIP | | ALTER VICTORY | | AMADEUS HOLY | –Plus– Another example of the pager command is when you want to estimate… 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…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
…of other benefits (non-blocking ALTER TABLE, for example). The benefit is that you have another …: 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: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… join together you may have serious issues because such tables do not get any indexes. Let me now show couple of examples. Assume we have the comments table which holds users comments to the blog, naturally containing user_id which left comment… JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt from user_counts uc, user_counts…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… and repeating it then repeating it for left or right half. For compressed block this…joins using string keys is so much slower compared to integer keys. Integer keys still best but for Innodb for example…select count(*) from t1, t1 t2 where t1.c=t2.c – simple join by string column select count(*) …
