June 18, 2013

Post: Database access Optimization in Web Applications.

September 12, 2006 By 10 Comments

…. Other common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 – If… set just because schema is not optimally indexed – this is easy. For example our ORDER BY rank query is such – adding index… doing so by several queries, assuming this query would not need to analyze much more rows because it is optimized differently. One…

Filed Under: Insight for Developers Tagged With:

Post: Sphinx search performance optimization: multi-threaded search

January 16, 2013 By 10 Comments

… local ones) and in parallel, merging of results is done by the aggregator and therefore it is single-threaded. Merging includes…-intensive operations such as ranking, ordering or even COUNT with GROUP BY and if data nodes return large amounts of data to… to split data, you may want these to be ranges by using a helper table in MySQL to define those ranges…

Filed Under: MySQL Tagged With: , , ,

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

…optimizer_switch=’index_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer…> ’1996-05-01′ GROUP BY o_orderkey ORDER BY num_items DESC LIMIT 10; …

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

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… has benefits and drawbacks. Benefit being you can get extra optimizations by optimizer knowing index is UNIQUE the drawback is insert buffer will… index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL gets…

Filed Under: Insight for DBAs, MySQL

Post: Distributed Set Processing with Shard-Query

May 14, 2011 By 27 Comments

…_date.Year IN (2008,2009) group by 1; –PARALLEL OPTIMIZATIONS: * Base level table name: `aggregation_tmp#74082863` * IN list optimization enabled * Detected an IN…_date.date_id) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count…

Filed Under: MySQL

Post: Missing Data - rows used to generate result set

July 20, 2008 By 15 Comments

… automatically !) there is potential for optimizing this query. For example: SELECT GENDER, COUNT(*) FROM PEOPLE GROUP BY GENDER This query will return only… and query SELECT GENDER, COUNT(*) FROM PEOPLE WHERE COUNTRY=’USA’ GROUP BY GENDER even though full table scan is performed only rows…

Filed Under: Insight for Developers Tagged With:

Post: Tools and Techniques for Index Design Webinar Questions Followup

October 4, 2012 By 5 Comments

… GROUP BY and an ORDER BY clause? Right; you’re stuck, unless your query groups by the same column as its sort column. You can optimize by adding the grouping… is relatively small, after the GROUP BY reduces it to one row per distinct value in the grouping column. Then sorting it may…

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

Post: Talking MySQL to Sphinx

April 19, 2009 By 4 Comments

… have not asked it. Another thing to try is GROUP BY – Sphinx executes GROUP BY in fixed memory which means results may be approximate…_id) as m,author_id as a from sptest group by author_id order by m desc limit 10; +————+——–+———-+———–+———+ | id | weight | forum_id… | +———+———–+ 10 rows in set (1 min 15.03 sec) Another optimization I wanted to check is the “early block reject” which…

Filed Under: Insight for Developers Tagged With:

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…page_lru where table_name like ‘%ssb%’ group by 1,2 ) sq order by pct_buffer_pool desc; +——————-+——————+——-+——+——+———+—————–+ | table_name | …| 0.001000 | 0 | 0 | 0 | 0 | 3 | 3 | | 8 | optimizing | 0.000048 | 0.000048 | 0.000000 | 0.000000 | 0 …

Filed Under: MySQL Tagged With: , , , , ,

Post: Air traffic queries in LucidDB

October 26, 2009 By 20 Comments

… AND “Year”=2007 GROUP BY “Carrier”) t JOIN (SELECT “Carrier”, count(*) AS c2 FROM OTP.”ontime” WHERE “Year”=2007 GROUP BY “Carrier”) t2 ON… OTP.”ontime” WHERE “DepDelay”>10 GROUP BY “Year”) t JOIN (select “Year”, count(*) as c2 from OTP.”ontime” GROUP BY “Year”) t2 ON (t…, which may signal that there some low-hanging fruits for optimization. OProfile can show clear places to fix.

Filed Under: Benchmarks, MySQL Tagged With: ,
« Older Posts
Newer Posts »