… 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…
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…
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…
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…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 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 …
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 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…
Post: Learning about MySQL Table Fragmentation
May 1, 2008 By 19 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 …
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 …
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be… as what other notes optimizer may wish to tell us. It is best seen by example: mysql> explain extended select * from sbtest where… about last query – even if it looks like there is a whole big deal of optimization being done query is executed extremely poor, completing…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…been several MySQL bugs opened about multi-core scalability (concurrent queries, autoincrement, concurrent… (150GB) that is showing poor performance for full table scans…without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole … “show innodb statusâ€. select * from table where id=5…
