…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 (SELECT parent, COUNT(*) AS Count FROM `menu…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…: Doesn’t the primary key solution for random selection only work when the IDs for movies are…count the movies per production year for each kind_id. It was more efficient in this case to force MySQL…Training classes in North America and Europe. Join Percona and the MySQL community at our events. The …
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
…, ACTIVE 3 sec fetching rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s…) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count from client_app_inventory cai inner join device d…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…` is low selectivity is not good for fast inserts, but it is suitable for range selects by `id…lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc…_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 …
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… with -A Database changed mysql> select count(*) from sakila; ERROR 1146 (42S02): Table ‘sakila.sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1 row…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(…select Year, count(*)*1000 as c1 from ontime_fact join dim_date using (date_id) WHERE DepDelay>10 GROUP BY Year) t JOIN (select Year, count…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count(*) pages, … from joining the tables in the other direction mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… of comments left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in set (0.00… will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2.cnt from user_counts uc, user_counts uc2…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… sec) 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…
