…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…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…of thousands of rows (or index entries) to do so. Other common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…COUNT(*) AS variations FROM index_usage GROUP BY query_id, db, tbl HAVING COUNT(*) > 1 ) AS qv USING(query_id, db, tbl); +———————-+————————————————————–+————+—–+———+ | query…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…number of these point primary key lookups will be depend on the number of rows…group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20; In…of a bug in MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
…number of rows: mysql> select avg(length(page)) from (select page from performance_log_080306 limit 10000) tmp; +——————-+ | avg(length(page)) | +——————-+ | 70.0444 | +——————-+ 1 row in…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…count the total number of words 4) count the frequency of words 5) order by the frequency of words, then by the md5 of the word, then by the md5 of…by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey GROUP BY… is that the above query would be reading 1/3 the number of rows in the table orders (left operand), and so MySQL 5.5 prefers to do a PRIMARY index scan of the table orders resulting in…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
…query a bit and get most popular storage engines by number of tables or number of rows instead of data stored. SELECT engine, count(*) tables, concat(round(sum(table_rows…
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
…count(*)*1000 as c1 from OTP.”ontime” WHERE “DepDelay”>10 GROUP BY “Year”) t JOIN (select “Year”, count(*) as c2 from OTP.”ontime” GROUP BY…MySQL via JDBC driver) just in SQL queries (compare with single LOAD DATA statement in InfoBright ICE edition). Also I am not so much in…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
…MySQL VIEWs ? mysql> create view user_counts as select user_id,count(*) cnt from comments group by user_id; Query OK, 0 rows affected (0.00 sec) mysql…
