June 18, 2013

Post: Goal driven performance optimization

December 22, 2008 By Leave a Comment

… in many cases to focus only on Server Side optimization – the Client Side Optimization is also quite important in particular for aggressive… factors to the response time of the problematic pages: mysql> select count(*),avg(wtime),avg(utime/wtime) cpu_ratio, avg(mysql…. Check the stats from the application above for example: mysql> select date_format(logged,’%H’) h,count(*),avg(wtime),avg(sphinx…

Filed Under: Insight for Developers Tagged With: ,

Post: Using delayed JOIN to optimize count(*) and LIMIT queries

April 6, 2007 By 22 Comments

…_increment, `pad` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) mysql> select count(*) from dim; +———-+ | count(*) | +———-+ | 30720 | +———-+ 1 row in set (0… sec) mysql> select count(*) from fact where i select count(*) from fact left join dim on val=id where i select i,pad from fact left join dim on val=id where i select i,pad from ( select i,val…

Filed Under: Insight for Developers Tagged With: ,

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

….00 sec) Highly selective columns only I thought I would emphasize – while it is a neat performance optimization for highly selective attributes, this… @ancientkey ancientkey_0′); | total_found | 2176 | | time | 0.434 | sphinx> select * from catalog where match(‘solar @ancientkey ancientkey_1′); | total_found…

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

Post: The MySQL optimizer, the OS cache, and sequential versus random I/O

April 28, 2008 By 17 Comments

… with the MySQL query optimizer. The optimizer tries to choose the … it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. +——-+———–+———–+———————————+ | table | type | rows | Extra | +——-+———–+———–+———————————+ | fact …

Filed Under: Insight for Developers Tagged With: ,

Post: Getting around optimizer limitations with an IN() list

January 9, 2010 By 9 Comments

… 50 AND 60 AND col_a = ‘set1′; +—-+————-+————-+——-+—————+———–+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref … is simply a missing feature of the MySQL optimizer – and it has to do with using x …

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

Post: Four ways to optimize paginated displays

September 24, 2008 By 27 Comments

… is it so expensive? I typically see queries like this: select …. from … order by …. limit X, 20 If the ORDER BY… this) or b) execute a separate SELECT to count the rows. There are ways to optimize so you don’t have to… list. With that in mind, here are four suggestions for optimizing paginated displays that can give significantly better performance. On the…

Filed Under: Insight for Developers Tagged With:

Post: A common problem when optimizing COUNT()

September 20, 2008 By 19 Comments

… what it’s trying to do. You can’t fully optimize a query unless you know how to consider alternative ways… is when the COUNT() contains a column name, like this: select count(col1) from table; If you know your SQL well… magic comment is embedded in the code next to them: select count(col1) from table; — COUNT_COL_OK Alas, there is…

Filed Under: Insight for Developers Tagged With: ,

Post: Percona Live MySQL Conference and Expo 2013 – News from the Committee – Tutorial Selection Complete

December 3, 2012 By 1 Comment

… the committee’s progress in selecting the program for the conference.  As many people know, the selections and schedule for tutorial day… developers in the world who have really dug into the optimizer and have a solid understanding of exactly how it works… every talk (except my own) just today.  Unlike the tutorial selection process where there was a few dozen proposals, there are…

Filed Under: Events and Announcements, MySQL

Post: Optimizing repeated subexpressions in MySQL

January 23, 2009 By 11 Comments

… made a little benchmark. The query looks something like this: select sql_no_cache pow(sum(rental_id), 1), pow(sum… an alternative query that returns the same result: select sql_no_cache pow(@sum := (select sum(rental_id) from sakila.rental), 1… runs 500 iterations in 12s. So it looks like the optimizer was computing the SUM() 18 times in the first query…

Filed Under: Insight for Developers Tagged With: ,

Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!

December 9, 2010 By 18 Comments

… ) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1 mysql> select * from a order by id limit 10; +—-+——————————————+ | id | c | …a | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.a | optimize | status | OK …

Filed Under: Insight for DBAs, MySQL Tagged With: , ,
« Older Posts
Newer Posts »