… Treating a general purpose computer as an SIMD computer is possible because…by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by… GROUP BY 1,2,3 ORDER BY NULL ) The other important optimization combines results from …
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… MV which computes the total sales for all customers. It computes this from the dashboard_top_customers MV, ordering them in descending order: mysql> call… ‘> order by total_price desc; ‘> ‘); Query OK, 1 row affected (0.00 sec) When the view is ‘enabled’, the contents are created: mysql… transaction id to which you have computed them. And then confirm they contain the same results mysql> select sum(total_lines) from dashboard…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…final result set. Intern-node communication One or more gearman queues are used to compute work. Computation…any type of compute resource which speaks SQL, but right now only MySQL storage nodes are …) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
…order by data limit 78000,1′ with, of course, the query cache disabled. I also verified that during the whole process, the computer…mysql -e “set session sort_buffer_size=32*1024*$i;select * from sorttest order by…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…performance of queries against a MySQL database by distributing the work …of queries by spreading the work over all available compute resources. In … but once again, the “scale-out” results are the most striking. — Q4 SELECT…BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard…
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
…computing resources, particularly in the cloud, results in decreased overall operational costs, so caches provide real value by…MySQL. Popular cache methods The MySQL query cache When the query cache is enabled, MySQL…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
…” and “lower” characters ! Yes I often use this too, in MySQL and in Postgres, it is just one of the tricks… the results in a temporary table. – since i use ORDER BY + LIMIT, I can then compute the max() or min() (depending on the order) or… have the ORDER BY field greater (or lower depending on the order) to the value computed above will not appear in the final result set…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…This might or might not be aggregation. Denormalize Pre-computing and careful denormalization need to go together. …order of your common queries and b) choose your primary key correctly. Let’s go back to the ad-by…or in MySQL 5.0 and earlier, doing the work on a slave, then piping the results back up…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…results you’re looking for. Here are couple of examples: GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY…summary table. With MySQL 5.0 it is also easy to implement one by use of triggers. … be able to use covering index to compute the rows. If the number of combinations is…
Post: MySQL 5.5.8 - in search of stability
January 3, 2011 By 11 Comments
… it from our TESTING area. In order to test different settings in …in the buffer pool. You can compute this value as Log sequence number …Percona Server result is ~2x better than the best result I received with MySQL 5.5…) to help increase throughput. Basically, by lowering innodb_max_dirty_pages_pct…
