… create six hash “buckets” in the table. This allows MySQL to set up a sequential scan … and 6 added to the where clause. This creates boundary conditions for our query. Any set … parallelism: select min(result) == 0 is_prime from ( select mod({$NUMBER_TO_FACTOR}/prime_number) from dataset where id …
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…MySQL 6.0 version. Query is pretty simple SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem WHERE…
Post: Derived Tables and Views Performance
August 31, 2006 By 16 Comments
… done mistake in select in from clause, ie forgotten join condition you might….02 sec) mysql> explain select * from (select * from test) t where i=5; +—-+————-+————+——+—————+——+———+——+———+————-+ | id | select_type | table | type | possible_…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
…from standpoint of how Flexviews works, is that triggers can not, under normal conditions… progress in a metadata table: $ mysql -e ‘select * from flexviews.binlog_consumer_status\G’ -uroot …TABLE where $SCHEMA is the schema in which the source table is located and $TABLE the name of the source table…
Comment: SELECT LOCK IN SHARE MODE and FOR UPDATE
November 20, 2007 By 51 Comments
…MySQL 5.0.45/Windows/Connector-J-5.1.5): Transaction-1 ————- SELECT p FROM assets WHERE asset.id=12 various other select…SELECT and UPDATE statements in Transaction-1 and hence the deadlock condition. The MySQL…
Post: Computing 95 percentile in MySQL
November 28, 2008 By 5 Comments
… the table, so how to get the data if MySQL only provides you the avg: mysql> select count(*),avg(wtime) from performance_log_081128 where page… about simply by running following order-by queries: mysql> select wtime from performance_log_081128 where page_type=’search’ order by wtime desc limit…, “systematic” delays, when response time is always bad in given conditions can be much worse problems. You do not want your…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… in these cases where you are joining a small table with a very large table with no ‘indexed where‘ conditions on the big table. BNLH takes… by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey WHERE…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…MySQL is accessing millions of rows: mysql> explain select * from v2 where c1 = 100; +—-+————-+————+——-+—————+——+———+——+———+————-+ | id | select_type | table…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… ref: const rows: 1 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: p type: eq_ref possible_keys… a range condition on the first (or the only) table, it works as expected: EXPLAIN SELECT COUNT(1) FROM items_ordered i WHERE i.itm… reports regarding this problem (e.g. #8569, #19548). Some replies from MySQL indicate this may be eventually fixed in 6.0 or…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
…MySQL, it’s best to show some execution plans on dummy data: EXPLAIN SELECT * FROM coordinates FORCE INDEX (x_y_col_a) WHERE…WHERE conditions (we are just no longer using an index on them): EXPLAIN SELECT * FROM coordinates WHERE…
