… randomness. The ORDER BY RAND() solution is known to have poor performance, but it returns a better random choice. Another workaround may be… temporary table to count the movies per production year for each kind_id. It was more efficient in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in the EXPLAIN…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…order by on another column. do i need to add the index on column using order by clause. A: If index is used for ORDER BY…MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… to complete in order to complete locking tables. This means…count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root | localhost | NULL | Query | 317 | Waiting for table flush | flush tables… which among other tools used by mysql client, so you might …way or another. If you’re just using Innodb tables and…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS “R/Call” FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
…order by to be fully indexed. An extra problem comes from the fact MySQL…count of matching rows which may be even trickier to slow for complex searches which generate a lot of rows. Another…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…MySQL data sets and queries. Another advantage is that it works with all MySQL…table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count… BY DayOfWeek ORDER BY c DESC; — Q2 SELECT DayOfWeek, count…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…The monthly summary is actually another MV. This saves a…order by total_price desc; ‘> ‘); Query OK, 1 row affected (0.00 sec) When the view is ‘enabled’, the contents are created: mysql…order_lines table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order_…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count…table_name like ‘%ssb%’ group by 1,2 ) sq order by pct_buffer_pool desc; +——————-+——————+——-+——+——+———+—————–+ | table…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… for OS cache to keep temporary table. Results: mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +—–+——————————————+ | cnt | c | +—–+——————————————+ | 2…) mysql> set tmp_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) cnt,c from gt group by c order by null… tables are moderately sized). Unfortunately I ran into another bug which means I will have to postpone this part of test to another…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… and for peaceful purposes. Love one another and let’s solve all the … which we will operate: create table words2 partition by hash(bucket) partitions 12 as select …by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by…
