June 19, 2013

Post: Joining many tables in MySQL - optimizer_search_depth

April 20, 2012 By 8 Comments

… making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used option which as per manual will chose best value automatically. Making this change I could bring optimization… is not clear how far from optimal plans do we get by using a greedy search. From the same discussion we can…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… any new features introduced with InnoDB full-text search), that’s largely where the similarities end. NOTE … | optimize_checkpoint_limit | 180 | | synced_doc_id | 1028261 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize…

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

Post: Goal driven performance optimization

December 22, 2008 By Leave a Comment

… more Client Side optimization you will need. For example if it takes you 30 seconds to generate the search results and 3 more seconds to load all style sheets images and render the page server side optimization is…)/wtime) lost_ratio from performance_log_081221 where page_type=’search‘ and wtime>1; +———-+—————–+——————+——————+——————+——————+ | count(*) | avg(wtime) | cpu_ratio | mysql_ratio…

Filed Under: Insight for Developers Tagged With: ,

Post: ORDER BY ... LIMIT Performance Optimization

September 1, 2006 By 100 Comments

… about. So what if you have application which can perform search on many different columns, with worse then perfect selectivity. Various… few members from the country, ie Slovenia – same kind of search might need to scan 1000s times more rows to provide… is indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity…

Filed Under: Insight for Developers Tagged With: ,

Post: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

… common causes of a poor Sphinx search performance I find our customers face is misuse of search filters. In this article I… While Sphinx is great for full text search, you can certainly go beyond full text search, but before you go there, it… thought I would emphasize – while it is a neat performance optimization for highly selective attributes, this is certainly not something you…

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

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… look too bad. MySQL is using three different indexes to search approximately 8100 out of 4.5M rows to return the… merge, it means that MySQL is really looking at a search space closer to 8.5M rows, not 8100, because it… can be modified globally or per-session: SET [GLOBAL|SESSION] optimizer_switch=”index_merge_intersection=off”; This is the database equivalent…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: Four ways to optimize paginated displays

September 24, 2008 By 27 Comments

… paginated display is one of the top optimization scenarios we see in the real world. Search results pages, leaderboards, and most-popular… found 13928 results. Let me look at the least relevant search results for my query.” Generally not — you usually look at… 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: Sphinx: Going Beyond full text search

July 23, 2007 By 18 Comments

… for scalable Full Text Search applications. For example on BoardReader we’re using this combination to build search against over 1 billion…). It is especially great as there is still room for optimization – Sphinx stores word positions in the index, while we do… databases rather than full text search applications. Group By was added to Sphinx so we could search number of matches per day…

Filed Under: Insight for Developers Tagged With: ,

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

January 9, 2010 By 9 Comments

…? Anyone out there created a zipcode database and created a “search within x numer of miles” function ? Thankful for any tips… wouldn’t scale. To understand why these sorts of geographic search queries are problematic in MySQL, it’s best to show… for this is simply a missing feature of the MySQL optimizer – and it has to do with using x BETWEEN 30…

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

Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization

September 17, 2007 By 14 Comments

… logs you would look at most popular web pages or search engine keywords which bring you most of the traffic. If… some silly like query to illustrate some search is required, so count(*) can’t be optimized away for MyISAM Tables. To show… implemented inside MySQL. Why am I looking on reporting performance optimization ? It is for ClickAider project which is growing rapidly and…

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