June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…an infinite variety of  queries.  The query optimizer analyzes queries for common patterns the MySQL designers know can be … query in a suboptimal form. Q: Doesn’t the primary key solution for random selection…ORDER BY RAND() solution is known to have poor performance, but it returns a better random…

Filed Under: Insight for Developers, MySQL, MySQL Webinars Tagged With: , , ,

Post: MySQL Limitations Part 3: Subqueries

October 25, 2010 By 21 Comments

… MySQL rewrites the query to make the inner query dependent on the outer query, which could be an optimization in some cases, but de-optimizes the query in many other cases. NOT IN(SELECT …) queries… servers also have poor subquery performance, or have had it in the past and have fixed it. And many MySQL users have…

Filed Under: Insight for Developers, MySQL Tagged With:

Post: A micro-benchmark of stored routines in MySQL

August 16, 2009 By 7 Comments

… co.Code = c; > return res; > end// mysql> delimiter ; Now the query can be rewritten as this: mysql> select sql_no_cache sum(ci.Population… query! (If you think of a better way to compare apples and uhm, apples… please comment). The poorly-optimized-subquery portion of the query…

Filed Under: Benchmarks Tagged With: , , ,

Post: Using flow control functions for performance monitoring queries

May 24, 2008 By 12 Comments

… in MySQL Queries as they are often abused used to create queries which are poorly readable as well as can hardly be optimized well by MySQL Optimizer… types of pages because they often have different performance profile. mysql> select count(*) cnt, avg(wtime) avw, sum(if(wtime>0.3…

Filed Under: Insight for DBAs Tagged With:

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

…most common causes of a poor Sphinx search performance I find our customers …in MySQL terms, Full text columns are always indexed and using the very powerful extended query syntax… selective columns only I thought I would emphasize – while it is a neat performance optimization for highly selective…

Filed Under: Insight for Developers, MySQL Tagged With: , ,

Post: When the subselect runs faster

March 18, 2010 By 9 Comments

… we had a query optimization request from one of our customer. The query was very simple like: SELECT * FROM `table`…cache well) When you want to run this query mysql first will try to find each row …query – use index to find rows. This is sensible strategy if there is no LIMIT, however it is poor…

Filed Under: Insight for Developers, MySQL Tagged With: , , , , ,

Post: Using any general purpose computer as a special purpose SIMD computer

May 16, 2011 By 5 Comments

… native database interface (MySQL): mysql> select word, md5(word), md5(…query. If your database system can not provide native parallelism, then performance will be poor. — SQL TO SEND TO SHARDS: Array ( [0] => SELECT… important optimization combines results from multiple queries together. This query …

Filed Under: MySQL

Post: Using index for ORDER BY vs restricting number of rows.

February 16, 2007 By 8 Comments

… with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using… `cat_id_2` (`cat_id`,`seller_id` ) mysql> explain select * from goods where cat_id=5 and seller…example queries is about 100 times so it may be quite serious. To fix this issue MySQL would…

Filed Under: Insight for Developers Tagged With:

Post: Learning about MySQL Table Fragmentation

May 1, 2008 By 20 Comments

…query: “SELECT count(*) FROM tbl WHERE non_idx_col=0″ I use this particular form of query… rate or you would see MySQL becoming CPU bound if IO …worth to notice you can see poor sequential scan performance even if… both fragmentation issues is the same – OPTIMIZE TABLE tbl – this command recreates …

Filed Under: Insight for DBAs Tagged With: ,

Post: Performance impact of complex queries

January 17, 2007 By 11 Comments

…MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It is not rare to see queries…optimize throughput before latency, meaning if you have query…poor. I remember in Linux 2.4 times I could make single full table scan query… master (selects often …

Filed Under: Insight for Developers Tagged With:
« Older Posts