June 19, 2013

Post: ORDER BY ... LIMIT Performance Optimization

… 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…

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

… 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…

Post: Goal driven performance optimization

… 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

Post: Sphinx: Going Beyond full text search

…. 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 …

Post: MySQL Indexing Best Practices: Webinar Questions Followup

… 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…

Post: High-Performance Click Analysis with MySQL

… 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, …

Comment: Using Sphinx as MySQL data retrieval accelerator

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…

Comment: EuroOSCON 2006 - High Performance FullText Search

… 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…

Post: What would make MySQL Multiple Queries Usable ?

… 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…

Post: Visualization tools for pt-query-digest tables

… 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…