…query – Query Cache does simple optimization to check if query can be cached. As I mentioned only SELECT queries…limits query cache effectiveness – if you have high write application such as forums, query…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… could due to to EC2 and time limits. I think the results at 20 nodes … simple substitution rules. So, to recap, every SQL query is really a cleverly disguised relational algebra … incremental materialized view optimizations can be applied. Shard-Query works only on sets Shard-Query takes relational …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… Refresh type Aggregation Outer join All SQL functions Built using SQL Requires FlexCDC COMPLETE Y Y Y … refresh will be more expensive. This is an optimization which is required to efficiently refresh MV …-> where order_id -> between 1 and 100 -> limit 500; Query OK, 484 rows affected (0.27 sec) …
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
…people. This information may often need simple select query, however what if you would like to …group by operation. The obvious optimization is to get rid of LIMIT 10 and just fetch all groups…find it quite inconvenient whenever it is MySQL limitation or SQL Standard. This would be extremely useful …
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
…query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql…limit 10) ORDER BY last_online desc limit 10; +—-+————–+————–+——+—————+——+———+——-+——-+—————-+ | …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…limitations in …; Query OK, 0 rows affected (0.00 sec) mysql> OPTIMIZE TABLE t; +——–+———-+———-+——————————————————————-+ |…sql $ mysqldump -uroot test –innodb-optimize-keys > dump_optimized.sql $ time mysql -uroot test < dump_unoptimized.sql…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 Comments
When we optimize clients’ SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it …> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY c LIMIT 5; Results with SQL_CALC_FOUND… to use two separate queries: mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5; The results…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… from our customers regarding query optimization… which had one thing in common – It is not query which needed to be optimized. Way too frequently… pretty inefficient. There is surely SQL_CALC_FOUND_ROWS but it can often make query slower than two queries, because it will not… to have queries index covered and avoid joins as much as possible. ORDER BY I already wrote about ORDER BY LIMIT Optimization so…
Post: Four ways to optimize paginated displays
September 24, 2008 By 26 Comments
…so expensive? I typically see queries like this: select …. from … order by …. limit X, 20 If the …and the total count, you either a) use SQL_CALC_FOUND_ROWS (see our post on this… query optimizations, there are two big things you can do. One is caching aggressively to prevent these queries from …
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…_size – Used by MyISAM tables only to optimize bulk inserts (multiple value inserts). Really helps…word_len, ft_min_word_len, ft_query_expansion_limit, ft_stopword_file FullText search related variables…. Currently MyISAM and Innodb. Some SQL level items as Query Cache also could use this feature….
