… by query, doing some stuff with results and then populating tables. This all worked well for small tables… but not for larger… by default mysql_query uses mysql_store_result C library call and buffers all result set in the process memory. Not good… anyway. The workaround for this one is to use SQL_BUFFER_RESULT hint if you need to release table locks early. It…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… change semantics of the queries – you always get actual query results. Really there are some chevats – if you’re not using… can set query_cache_type to “DEMAND” and use only SQL_CACHE hint for queries which you want to have cached…, obviously there is no upper boundary – Very complex queries producing small result set will be offering best speed up. So when it…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… bother to sort results, though this has a little difference in this case as number of groups is relatively small: mysql> select… you have relatively small number of groups which you can fetch on the application side (you can store result in temporary table… I forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about same…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
…the new SpecJAppServer Results More information from Tom Daly can be found here These results are quite …in /etc/my.cnf (included in FDA) [mysqld] sql-mode = IGNORE_SPACE transaction-isolation = READ-COMMITTED …default. If you have queries which only do small sorts it is indeed better to have …
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… important notes about this table before we get to results – this is rather small table which fits in memory in all cases… test was a result of my curiosity. I’ve tried to order results by states. 1) Results for ENUM: select SQL_NO_CACHE city…, 5; Result time(mean): 0.003125 2) Results for VARCHAR: select SQL_NO_CACHE city, state from cities_varchar limit 10000, 5; Result time…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… access to the current LSN (log_sys->lsn), with a small wrinkle: the accesses are actually protected. log_write_low asserts…: get_schema_tables_result(JOIN*, enum_schema_table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090…*, st_lex*, select_result*, unsigned long) (sql_select.cc:269) ==9090== by 0x63A3E3: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5179…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… three numbers are wall clock time (as calculated by microtime()), SQL execution time, and parse time, respectively. Actually performance is a…, a final pass over the final result may be necessary, and this may add a small amount of serialization at the end… ) This powers the UPSERT. The results from the six branches are combined with this. — AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… 20 nodes Distributed set processing (theory) What is SQL? As you probably know, SQL stands for “structured query language”. It isn’t… maximum conclusion, splitting a problem up into many small problems and then putting the results back together again. This set logic allows… speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the distributed processing. The results from…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… lot of flexibility to get the results you want, but more quickly. More on this later. SQL features used in the query… view, even when the number of changes is very small. The Flexviews SQL_API Unlike views which use the complete refresh method… have computed them. And then confirm they contain the same results mysql> select sum(total_lines) from dashboard_customer_sales ; +——————+ | sum…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…-infobright-demo i686 ICE 3.5.2pl1 32bit. Requires m1.small or greater. snap-073b6e68 shard-query-demo-data-flatfiles 30GB… the ./run_query command, called pivot_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results & [1] 12359 $ tail -f ./raw — Q1 … At the end, you will get a result output that…
