…Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where…multiple table and the column type was inconsistent between them… so inserts into one table would happen with correct value…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…join_buffer_size, then multiple hash tables would be …than this value. Hence, the reason I have set join_…selective WHERE clause on an indexed column The SQL used for this test together with its EXPLAIN output as returned by MySQL… ideal because you have a highly selective where clause that reduces the…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…have populated this table with random data for i and j having both of them having 1000 of distinct values…where clause which covers multiple columns: Q1 SELECT sum(length(val)) FROM T WHERE i=2 AND j=1 Q2 SELECT sum(length(val)) FROM T WHERE…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…not have to have any… to the multiple levels of …now only MySQL storage …WHERE clause that uses an IN clause: — INPUT SQL: select…VALUES(`origin_airport_id`), `count(*)`=`count(*)` + VALUES(`count(*)`), `sum(AirTime)`=`sum(AirTime)` + VALUES(`sum(AirTime)`), `sum(DepDelay)`=`sum(DepDelay)` + VALUES…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT name… where clause at all if it has just few values. For example if we would have gender instead of age with just two possible values…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…clauses. MySQL…have multiple ages to deal with ether as BETWEEN range or as IN filesort appears and query becomes very slow: mysql> explain select…mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION ALL (select * from people where…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…value to lookup the full row data corresponding to the given index entry. So what choices does MySQL have if you have…selectivity and correlation. If where clause from first column selects 5% of the rows and applying where clause…Index merge works much better than multiple column indexes. This is in …
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…clause on the previous key part. So if you have INDEX(A,B) and have a where clause…where 1 row in set (0.01 sec) mysql> select sum(events) from info where…multiple group remains question to check with MySQL…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…select/group by/join/where. You may not use sub-queries or any non-deterministic functions like NOW() or RAND(). HAVING clauses, ORDER BY clauses…value, then it refreshes the view up to the latest changes which have been collected. You can use this to refresh multiple…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N,M Make sure it uses index It is very important to have… as there are multiple category_id values in the list …stick to simple MySQL using multiple indexes on most selective columns would …if you do not have full where clause resolved by index is …
