On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register to… to count the movies per production year for each kind_id. It was more efficient in this case to force MySQL to… America and Europe. Join Percona and the MySQL community at our events. The next one is in London on November 11-12, 2013…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…) { $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` GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=” . $parent); echo “”; while ($row = mysql…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101…_id, COUNT(1) as installed_count from client_app_inventory cai inner join device d on (cai.client_id = d.client_id) inner join label_to_device ld on…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…This has no effect on standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication? It’… SET count = $last_count where achievement = ‘killed_troll’”; } change your schema In Example 2, above, how above moving the ‘joined‘ column …
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…outperforms FlashCache by over 2x, on average. Looking just at sysbench…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_…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
….lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 –report… = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size… = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… on the field that is used to join the two tables? A: The question in this case is how MySQL will execute the join…, nothing more. Would doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…_pool from ( select table_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number… from joining the tables in the other direction mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join lineorder on… (22.54 sec) mysql> explain select straight_join sum(lo_extendedprice*lo_discount) as revenue -> from dim_date join lineorder on lo_orderdatekey = d…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… as returned by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l… as returned by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l… as returned by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… 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…
