… 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…['label'] . “”; display_menu($row['id'], $level + 1); echo “”; } elseif ($row['Count']==0) { echo “” . $row['label'] . “”; } else; } echo “”; } display_menu(0, 1…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…query patterns the optimizer recognizes, and which are left to the developer to refactor manually. There …was that it created a temporary table to count the movies per production year for each kind… Training classes in North America and Europe. Join Percona and the MySQL community at our events….
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…all these various tests. Clockwise from the top left in the next graph, we have “no cache”, “…=32 –test=tests/db/oltp.lua –oltp_tables_count=32 \ –oltp-table-size=10000000 –rand-init=on …max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 …
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 dim…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey WHERE s_nationkey… is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey WHERE l_shipmode… 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…
Comment: Why MySQL could be slow with large tables ?
April 12, 2007 By 141 Comments
…JOIN tblanswersets ASets USING (answersetid) INNER JOIN tblanswersetsanswers_x ASAX USING (answersetid) INNER JOIN tblanswers A USING (answerid) LEFT JOIN (tblevalanswerresults e1 INNER JOIN…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… 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… (42S02): Table ‘sakila.sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1 row in set (0.00 sec) mysql…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…_name as INDEX_NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by…, all_indexes.index_name as index_name from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where… / ( 1024 * 1024 ), 2) as index_size_mb from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56 from var55 left join var56 on var55.variable…-5.6.10-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN_BUFFER_SIZE | 131072 | 262144 | | TMPDIR | /mnt/data/sandboxes/msb_5….5 default values. Such as performance_schema auto scales to count up to 445 tables and 224 threads in this case…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
October 19, 2008 By 22 Comments
… delayed JOIN to optimize count(*) and LIMIT queries… If you’re executing count(*) queries for such result sets MySQL will perform the join even if you use LEFT JOIN…
