…. Q: in trick #1 will “WHERE a IN (2-4)” be worse then “WHERE a IN (2,3,4)”? Another word is range for IN clause better than BETWEEN? A: IN(2… the two tables? A: The question in this case is how MySQL will execute the join. If it will fist lookup Table2 using…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…the distributed reduction discussed above. If you have any limitation in resources in a cluster (cpu, memory,…which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law…with aggregation, a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_id…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… perform sort results retrieved from MySQL when your WHERE clause goes beyound col=const values which would allow MySQL to still use second portion… of single query works in this case: mysql> explain (select * from utest where c1=5) union (select * from utest where c2=5) order by… enough in this case to “dive into” the union and add ORDER BY ORD LIMIT 10 to individual queries. What if we…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… to become SELECT with appropriate where clause. This however would not tell you full story, especially now as MySQL has triggers which can…, so if you made an error in select in from clause EXPLAIN might never complete. Solution is to run EXPLAIN for statements in FROM clause separately…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… example if you’re just using ORDER BY without and where clauses on the table. In such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the… queries even if data is in memory. If MySQL is only reading index and not accessing rows you will see “using index” in EXPLAIN output…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…IN clauses. MySQL has to internally build all possible combinations for row retrieval which ma become very slow if IN lists are large. Take 3 IN…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… which only have prefixes in the index. Notice “Using Index” in Extra column. mysql> explain select j,k from t where i=5 \G… but MySQL does not look at the actual data in this case it only looks at definitions. mysql> explain select k from t where…’re using column j in where clause. Even if this particular like check can be done only by using index, MySQL is not smart enough…
Post: Recovering from a bad UPDATE statement
October 19, 2012 By 5 Comments
… a WHERE clause? Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement? This is possible if you are running in binlog… binlog_format=ROW — MIXED doesn’t help as in my testing as MySQL chooses to binlog UPDATEs in STATEMENT format when in MIXED mode.
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… to handle the output of each input asynchronously, if you like. Right now I believe this only … “buckets” in the table. This allows MySQL to set up a sequential scan over the items in this bucket, … with BETWEEN 1 and 6 added to the where clause. This creates boundary conditions for our query. Any …
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
… again and again. Today I’ve read opinion that if we have clause WHERE has_something=1 we should have index on column… times slower. Good that mysql in this case choose do not use index mysql> explain select count(name) from testr where has_something=0… slower. and this time mysql is going to use index in execution plan: mysql> explain select count(name) from testr where has_something=0…
