June 19, 2013

Post: ORDER BY ... LIMIT Performance Optimization

September 1, 2006 By 100 Comments

… application which can perform search on many different columns, with worse then perfect selectivity. Various social networking and dating sites are perfect… 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: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… was the only storage engine with support for full-text search (FTS). And I’ve encountered many customers for whom the… same (modulo any new features introduced with InnoDB full-text search), that’s largely where the similarities end. NOTE 1: I… up to date by setting innodb_optimize_fulltext_only = 1 and then running OPTIMIZE TABLE, and that we might have to run OPTIMIZE TABLE…

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… date_format(logged,’%H’) h,count(*),avg(wtime),avg(sphinx_time/wtime) sphinx_ratio from performance_log_081221 where page_type=’search…

Filed Under: Insight for Developers Tagged With: ,

Post: Sphinx: Going Beyond full text search

July 23, 2007 By 18 Comments

…. You might point out if we had link_date between X and Y and link like “prefix%” …is especially great as there is still room for optimization – Sphinx stores word positions in the index, while … than full text search applications. Group By was added to Sphinx so we could search number of matches …

Filed Under: Insight for Developers Tagged With: ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… common for a date range to also need to be ordered. The question is how can one have optimized indexes and sorting… comparison as a search condition WHERE A=5 ORDER BY B will use index (A,B) for sorting optimization, for more complicated… concerns or tricks for selecting using some date ranges? or between dates? or after a date? A: Date comparisons work very similar to other…

Filed Under: Insight for DBAs, MySQL

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, …still another reason to use InnoDB/XtraDB tables… Optimize For I/O It is pretty much inevitable…create table ads_by_day_by_blueness ( day date not null, ad int unsigned not null, …

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

Comment: Using Sphinx as MySQL data retrieval accelerator

November 8, 2006 By 25 Comments

Right. ORDER BY Is important and can be optimized and paralellized however it is not done yet and it … search and only sort these by date. This would not guaranty you sorting by date. I however can see you building indexes in Date… the point of this post. Regarding cheating and full text search in general. Generally you want to provide results which are…

Filed Under: Insight for Developers Tagged With: ,

Comment: EuroOSCON 2006 - High Performance FullText Search

September 23, 2006 By 4 Comments

… with mysql fulltext were that my searches performed: * Using boolean search. * No stopword-list. * Sorting hits by date and not “relevance” since relevance… it was boolean search), then sort the result on date and finally return top 200 matches (instead of having some optimization to loop…

Filed Under: Events and Announcements Tagged With: ,

Post: What would make MySQL Multiple Queries Usable ?

September 5, 2007 By 1 Comment

… is more niche optimization but it can be very helpful for some applications. On BoardReader for example while performing search we also want to get result search result set, as well as same set… to group by date to show history of matches per day. This could be very well optimized by performing search query only once…

Filed Under: Insight for Developers Tagged With:

Post: Visualization tools for pt-query-digest tables

August 31, 2012 By 6 Comments

… first been seen since the specified date or date range. Also you can explain …it easier to figure out what to optimize and how to track performance over …by different query conditions; * graphing reports; * explaining queries; * searching and displaying samples; * displaying table status and CREATE…

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