…of lines in the order_lines table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
…two separate queries: one – to get a result set, another – to count total number of rows. In…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…row in set, 1 warning (0.00 sec) Notice the rewritten SQL query…table: mysql> select * from v2; +——+———-+ | c1 | count(*) | +——+———-+ | 10 | 130 | +——+———-+ 1 row in set (0.01 sec) Notice the vastly different plan over the old one…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…table). In order to demonstrate the power of Shard-Query it is important to test complex queries…row in set (0.02 sec) Each year has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ | 135125787 | +———–+ 1 row in…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…COUNT 5-10 times faster, sometimes more. This however still works up to certain number of rows in result set, with results set of 100.000+ it becomes pretty inefficient. There is surely SQL_CALC_FOUND_ROWS but it can often make query slower than two queries…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…tables in a database, so you can query them with SQL. I logged all queries…rows in set (0.00 sec) Here’s how to read this — the first row says that query 1262633894049058504 has two variations. One…
Comment: Why MySQL could be slow with large tables ?
December 4, 2008 By 141 Comments
…of rows with “Bigland” was just 75K rows compared to rows with “ServiceA” which was 50K rows. The initial table (unit) was 100K rows. Each row…ONE JOIN extra, with an additional 75K rows to JOIN, the query went from OK to a DISASTER!!!! Then number of rows…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…one is allocated per-join-per-thread, in special cases. A join buffer is allocated to cache rows from each table in…query joins several tables this way, you’ll get several join buffers allocated, for example, this one will have two: select * from a_table…in sql/sql_select.cc in…tables,uint table_count…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
…of rows which matches our where clause. Most Simple Way Number One is to simply run two queries: mysql> select grp, count… | | 6950 | 290 | +——+———+ 11 rows in set (29.68 sec) Use of extra temporary table …row in set (0.00 sec) As I forced FileSort execution method for GROUP BY by using SQL…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
…row in any table, the query cache entries for every query which accessed that table must be invalidated. If a frequently executed or expensive query…count of items sold. A query may have to access many millions of rows of…
