…row in set (0.00 sec) mysql> select count(*) from fact where val like “%c%”; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (8.79 sec) mysql> select count… about MySQL 5.0 Query Cache: Whitespace at the start of query does not block query from being cached. Moreover query …
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
… MySQL has cached number of rows in this table. This is why it is able to instantly answer COUNT(*) and COUNT(val2) queries, but not COUNT… more queries: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count… key(i,val); Query OK, 7340032 rows affected (37.15 sec) Records: 7340032 Duplicates: 0 Warnings: 0 mysql> select count(val) from fact where…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row in … an example of such a query. . Query details: — Q1 SELECT DayOfWeek, count(*) AS c from ontime_fact…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…query would take significantly longer (40+ minutes): mysql> SELECT -> customer_id as `customer_id`, -> SUM(price * quantity) as `total_price`, -> COUNT…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query_cache_sizeblogpercona.commanual query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… points you need to attack the right query in the right way. But vanilla MySQL does have its limitations, it reports only…] @ localhost [] # Query_time: 2.365434 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 655360 use test; SET timestamp=1325145746; select count(*) from auto… run time, query ranked #1. The first row in the table above shows the Count of number of times this query was executed. Now…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
… queries: (again look on comparison with other engines in Google Spreadsheet or in summary table at the bottom) Query Q1: mysql> SELECT DayOfWeek, count… DayOfWeek ORDER BY c DESC; 7 rows in set (6.79 sec) Query Q2: mysql> SELECT DayOfWeek, count(*) AS c FROM ontime WHERE DepDelay… ORDER BY c DESC LIMIT 10; 4.96 sec Query Q4: mysql> SELECT Carrier, count(*) FROM ontime WHERE DepDelay > 10 AND YearD=2007…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…querying INFORMATION_SCHEMA. For instance, counting the number of sleeping connections can be done with: mysql> SELECT COUNT…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… work with rows. They break the queries up into the lowest level sets possible (rows) which …which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law…query with aggregation, a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_id, count…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 19 Comments
… with COUNT(). This is database-agnostic, not related to MySQL. The problem is when the COUNT() contains a column name, like this: select count… of rows. The problem with that is that you don’t know by looking at the query whether the developer wanted to count rows or values — or, quite possibly, the number of distinct values in that column. You see, there’s another form for COUNT…
