… Doesn’t the primary key solution for random selection only work when the IDs for movies…` by a secondary index. But the result was that it created a temporary table to count…grouping by kind_id in index order. This made the first table in the EXPLAIN seem like it was scanning more rows. But by…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… 9 primary key SELECTs followed by a PK UPDATE …= 2000M innodb_log_files_in_group = 2 innodb_file_per_table…select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT…selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
… highest number of follow and order by follow。 I wrote: SELECT follow,count(*) AS NUM FROM wp_fans GROUP BY follow order by NUM desc limit 5 Follow…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… case as number of groups is relatively small: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by null; +——-+—–+ | grp | cnt… grouped result set together with total value for the groups: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp with rollup order by…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
… GROUP BY Origin ORDER BY c DESC LIMIT 10; 4.96 sec Query Q4: mysql> SELECT Carrier, count(*) FROM ontime WHERE DepDelay > 10 AND YearD=2007 GROUP BY Carrier ORDER BY…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… mostly unique “c” column. mysql> explain select count(*) cnt,c from gt group by c order by null limit 10; +—-+————-+——-+——+—————+——+———+——+———+—————–+ | id | select_type | table | type | possible_keys… 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 | 80c9a87595687ccb33fa525d396ee75658aec777…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… a view avoids aggregation, DISTINCT, GROUP BY, ORDER BY, etc, then it can use … extended select c1, count(*) from v1 where c1 = 10 group by c1; +—-+————-+——-+——+—————+——+———+——-+——+———-+————————–+ | id | select_type | table | …
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…I used for MySQL Group by Performance Tests to see … following speed: mysql> select * from gt order by i desc limit 10; +——–+——————————————+ | i | c | +——–+——————————————+ | … status output MySQL only counts completely sorted rows in …
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… really matters is amount of rows are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return… so. Other common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 – If there…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…’; +———-+ | COUNT(*) | +———-+ | 320 | +———-+ and counting the number of connection for each status can be done with: mysql> SELECT COMMAND,COUNT(*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY COMMAND ORDER BY TOTAL…
