…-rel27.1/sql/sql_parse.cc:3138 3138 res= handle_select(thd, lex, sel_result, OPTION_SETUP…id = 0, query_strip_comments = {buffer = 0×0, length = 0, buffer_length = 0}, mark_used_columns = … = {string = { str = 0x7f3e84004ba0 “INSERT INTO testdb_N . t1_temp1_N SELECT * FROM test.table10_int_autoinc”, …
Post: How to obtain the "LES" (Last Executed Statement) from an Optimized Core Dump?
September 9, 2012 By 9 Comments
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
…result set we want from single query ? Sure. Here is Way Number Three: mysql> select * from (select… extra temporary table for buffering helps us to get result set we’re looking… events. UPDATE: Looking a bit further into it I found why GROUP …method for GROUP BY by using SQL_BIG_RESULT hint I can see …
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
…_buffer_pool_size to 50% of my RAM, and then when I tested MyISAM FT index, I reallocated that memory to into key_buffer_size. The other thing that happened with regards to… use the DataImportHandler to import the result of any SQL query, and if you can form a SELECT query that returns the “new…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…into a quite interesting (although not very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT…the buffer pool did not affect the results. …of rows read at SQL layer is actually …SELECT will only be faster when the condition is very selective and the buffer…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
… want to split the file into pieces for the load for … bloated and slow. Even simple SELECT queries might have to scan … the buffer pool and InnoDB essentially starts swapping between its buffer pool…result of loading the entire 4GB file in one chunk: time mysql -e “set foreign_key_checks=0; set sql…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
… SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query is executed for example SELECT… sort you can do SET sort_buffer_size=50000000 before executing query and SET sort_buffer_size=DEFAULT after executing the query… query is executed, so I could do something like: SELECT SQL_SORT_BUFFER_SIZE=50000000 NAME FROM LARGE_TABLE ORDER BY NAME DESC…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
… – you may notice wrong query results days after crash. Sometimes corrupted …restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are…OS cache. You can save these into .sql file and use –init-file …warmup this data you might run select 1 from _table_ limit 1 …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… of instances that evenly divides into 20 for best results. There is a helpful utility…exec time, the third parse time). $ echo “select count(*) from ontime_fact;” | ./run_query Array ( [count(*)] …buffer-size=512K read-rnd-buffer-size=1M skip-host-cache skip-name-resolve sort-buffer-size=512K sql…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(c) SELECT…| restoring secondary keys | 17.824109 | | rename result table | 0.162041 | | end | 0.000008… not fit in the buffer pool, fast index …> dump_optimized.sql $ time mysql -uroot test < dump_unoptimized.sql …
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
… and fetching rows from the buffer pool, with respect to a single… used in this earlier post into a partitioned InnoDB table. Partitioning example:…. It runs the SQL via ShardQuery and it prints the results. Test #1 …. For example (the final query): select count(*) from ontime.ontime where FlightDate…
