… of range for unsigned column and I would expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select… very quickly and select queries for values over 2bil becoming increasingly more expensive. Because there would be more and more queries for values…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…exactly happens during query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_…deal with ether as BETWEEN range or as IN filesort appears and query becomes very slow: mysql> explain select * from people where age=…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal…my query is only selecting on vendor, will the index have any impact on the speed of the query? …Is there any special concerns or tricks for selecting using some date ranges? or between dates? or after a …
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
… employees | range…queries, making the latter more performant than the former. Of course you can stil break all this UNION queries down and SELECT…SELECT .. UNION SELECT INTO OUTFILE should be convenient. Lastly to demonstrate the purpose of this post. mysql [localhost] {msandbox} (employees) > SELECT…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… the ages from the range and look at all zip codes supplied. In practice – it will not: mysql> explain SELECT name FROM people… had increased to 8. Lets see how query times differ in these cases: mysql> SELECT sql_no_cache name FROM people WHERE age… give a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name FROM people WHERE…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…, so range 1..100 selects about 1% of the table. The times for full table scan vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select…. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by one for query execution, which…
Post: MySQL performance: Impact of memory allocators (Part 2)
March 8, 2013 By 12 Comments
…_RO test consists of 5 select queries – select_ranges, select_order_ranges, select_distinct_ranges, select_sum_ranges, point_select. Processing these queries will involve notable amount … with decreasing performance in workloads with MySQL. # Test: POINT_SELECT:throughput, QPS # # Set 1 – 5.5….
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
…mysql> insert into article_comment values(4300000002,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +——————-+——-+ 4 rows in set (0.00 sec) set sort_buffer_size=10000000; mysql> select * from… large sort_buffer_size ? mysql> set sort_buffer_size=100000000; Query OK, 0 rows affected (0.00 sec) mysql> select * from gt order by…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… as the conversion is bidirectional An range operation over a set of integers …as run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count… important optimization combines results from multiple queries together. This query is single threaded, and thus …
