… looking for. WITH CHECK OPTION is used to ensure we do not insert data in the view which will be invisible. The… could think MySQL would look at the WHERE clause to figure out which value should be used but unfortunately it does not work… can you do now ? The obvious option is to go over the code and change inserts so they go in the base…
Comment: Innodb Performance Optimization Basics
November 2, 2007 By 75 Comments
…, What I’m saying is if UserID is primary key in Innodb table the key on (Email) is internally (Email,UserID) because PK value is always stored in the index and rows are stored by it for same… id also can be used for covering index, where clause and I think it is being fixed for filesort now. See this post for…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_custkey` (`o_custkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Test Cases Now let’s see the test cases and then see how… a small table that fits in memory to a large table with no WHERE clause The SQL used for this test together with its… table that fits in memory to a large table with a selective WHERE clause on an indexed column The SQL used for this test…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… key lookup are satisfied in the where clause. If a ‘const’ table contains no rows, and it is not used in an OUTER JOIN, then… returned. MySQL does this by adding the WHERE clause in the query with ‘where 0′. Let’s now look at what happens after the value… is the WHERE clause. You will see that the SELECT statement used in the view has been modified to include the WHERE clause that I used when…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… a where clause A BETWEEN 5 and 10 AND B=6 only the first part (A) of the index will be used which… part is used. Let us now replace the range with IN list in this query: mysql> explain select sum(events) from info where d IN(’2007… one. In the real queries you can use table with ids just as we had table of days with a where clause instead of…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec) If you take a closer look to WHERE clause you will find the reason – date(d) equals to date(now()) which is constant which means… if we have function in ORDER BY and exactly the same function is equals to constant by WHERE clause. If course it works…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… of queries which contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use of the multiple… in the WHERE clause that are included in this merge operation and then perform a set intersection on the results. Here’s one situation where…’s quick, and it will get you back in the house, but now you have no door, and your cats might get…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… into subsets using basic relational algebra substitution rules that are documented in this blog post. This allows BETWEEN, IN, subqueries in the FROM clause, and…. In the future you will be able to distribute work over any type of compute resource which speaks SQL, but right now… a simple query with aggregation, a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_id, count(*), sum(AirTime…
Post: Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
…” we could not use link=const where clause but had to use link like “prefix%” which means index could not be used to get 20… where clause we would not be able to use index past link_date part, it is true so we had to use link_date in… which would allow to same on sorting which is now still happening. Using Sphinx such non-traditional way required implementing some features…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…statement to become SELECT with appropriate where clause. This however would not tell you full story, especially now as MySQL has triggers which…functions is also not used in optimization which may produce suboptimal plans if stored functions are used in queries. In general in my opinion EXPLAIN …
