…with and without LIMIT clause, and tested queries which need to read the data or…rows (more details with Jim Starkey’s comments in Part 2). There …SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d READ_KEY_RANGE_NO_DATA_LIMIT Query: SELECT city FROM $tableName WHERE…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… the SELECT wp_options query, this is basically a unique way of identifying the query and simply implies that this is a SELECT…_one `.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’\G This is the actual…, or you might even compress the values stored in the option_value column so that less data is read and sent. Let’s…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… comments, personnel org charts, or nested bill-of-materials. Sometimes it’s tricky to do this in SQL and still run efficient queries… WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
…SELECT * FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30 and 40 AND y BETWEEN 50 AND 60 AND col_a = ‘set1′; +—-+————-+————-+——-+—————+———–+———+——+——+————-+ | id | select…or Y, and…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
…select * from utest where c1=5 or c2=5 order by ord desc limit 10 \G *************************** 1. row *************************** id: 1 select…and when only takes 10 rows from it: mysql> explain (select * from utest where c1=5 ) union (select * from utest where…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
…SELECT … UNION SELECT, where do you put the the INTO OUTFILE clause? On the first SELECT, on the last or…FROM ( -> SELECT * FROM employees -> WHERE hire_date BETWEEN ’1990-01-01′ AND ’1990-01-02′ -> UNION ALL -> SELECT * FROM employees -> WHERE hire_date BETWEEN ’1990-01-05′ AND…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
…, I set my InnoDB buffer pool to 256MB and created a table that’s large enough to test, but small enough to…; insert into t(a) values(current_date); insert into t select * from t; I repeated the last statement until it got slow… WHERE clause, mysql> select count(*) from t where a = current_date and left(a, 10) = ’2008-10-29′; The average time is 9.39 seconds, or…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…or a mapped subset of nodes) the result set is aggregated using distributable aggregate functions. The results from… supported. Amdahl’s law applies to the distributed processing. The results from the slowest …, a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…and where is our tablespace located (-f) The row format can be -4 (REDUNDANT) or -5 (COMPACT). From…table_defs.h it’s the moment to compile… mysql (employees) > select * from salaries where emp_no=10008; +——–+——–+————+————+ | emp_no | salary | from_date | to_date | …
Post: Gathering queries from a server with Maatkit and tcpdump
July 1, 2009 By 23 Comments
… doesn’t require any Net::Pcap or other modules from CPAN. It’s written in pure Perl, and it parses the output of tcpdump…) from dual ) as x\G Indeed, it’s no surprise the query took a tenth of a second to execute, and now you see where “SELECT dual” comes from. Notice…
