…_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_… = 2 innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 50 innodb_io_capacity =…by query cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL…
Post: Should MySQL Extend GROUP BY Syntax ?
August 17, 2007 By 11 Comments
…value in the group: This is one illustration of group by limitations in SQL language which is not offset by any MySQL specific extensions,yet…re soring by population you would see MAX(Population) in the population column: SELECT City, Population City FROM City GROUP BY Country GROUPORDER BY …
Post: Wrong GROUP BY makes your queries fragile
September 6, 2006 By 7 Comments
… it, especially as it is connected to MySQL Performance. No it might not affect MySQL Performance per say but it limits our… id,login,max(login_time) FROM log GROUP BY id,login May be replaced by SELECT id,login,max(login_time) FROM log GROUP BY id which may be faster especially if GROUP BY…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… years, which can be confirmed by examining the contents of the date dimension: mysql> select count(*), min(FlightDate), max(FlightDate) from dim_dateG… BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve when grouping and filtering is…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
… not important. Sphinx mysql> select max(forum_id) as m,author_id as a from sptest group by author_id order by m desc limit… (32.47 sec) MySQL mysql> select max(forum_id) as m,author_id as a from sptest group by author_id order by m desc limit… both of them – it is easy to “break” MySQL making it to do group by with on disk temporary table which will make…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
…by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by… such a degree. mysql> select min(id),max(id) from words; +———+———+ | min(id) | max(id) | +———+———+ | 1 | 3088896 | +———+———+ …
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…keep temporary table. Results: mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +—–+——————————————+ | cnt | c | … sec) mysql> set global max_heap_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… if you didn’t take it apart. Projection, GROUP BY, SUM, COUNT, MIN*, and MAX* are distributable. With a little elbow grease, all… of compute resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the…` FROM `aggregation_tmp_74082863` GROUP BY 1 –POST EXEC: DROP TABLE IF EXISTS `aggregation_tmp#74082863` ; Edit *MIN/MAX are only distributable in…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total_price desc -> LIMIT…MAX/COUNT_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct values for each group…high +——————+ 1 row in set (0.68 sec) mysql> select sum(total_lines) from…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…-driver=mysql –num-threads=1 –max-requests=0 –oltp-dist-type=uniform –max-time=180 –oltp-read-only –mysql-host=localhost –mysql-table-engine=innodb –mysql…_index as p using(index_id) where t.schema=’test’ group by i.index_id \G INDEX_NAME DATA_SIZE_MB test… we can see in default configuration MySQL has buffer pool which can be easily washed away by large table scans or heavy…
