The She-BAOne TRIGGER Fact, Optimizer Rewriting Stuff
| BEFORE AFTER |
INSERT UPDATE DELETE |
That was brought up at the User Group meeting.
In other news, I have been reading the MySQL Internals Manual. The most interesting chapter yet is Chapter 3, the optimizer. This can be read out of order, and actually contains lots of information that folks can use. For instance:
WHERE col1=col2 AND col2='x'
transforms to
WHERE col1='x' AND col2='x'
because constant matching is faster.
Tautologies and always-false statements are usually optimized out of the query so that something like WHERE 0=0 does not waste time by actually being tested.
A table with 0 or 1 rows is a constant value. Another constant table scenario is WHERE col1=[constant] for all the columns of any UNIQUE or PRIMARY KEYS, assuming they are defined as NOT NULL. This is a good reason to use NOT NULL for column definitions unless you really need NULL.
A constant table is what is used by the optimizer when you see "const" as the "join type" in the result of an EXPLAIN.
WHERE col1 in (1,2,3) is the exact same expression as WHERE col1=1 OR col1=2 OR col1=3
WHERE col1 BETWEEN 1 AND 3 is the exact same expression as WHERE col1>=1 AND col1< =3
The latter 2 examples are good to know, because the first of the two statements in each case are more clear.
I am thanks to this because
I am thanks to this because we have cleared out some doubts and clarifications. nice features.
Try: select * from table1
Try:
select * from table1 where primaryKey IN (select primaryKey from table2)
Assuming table2 has more than 1 row, the query will not use the primary key index for table1. Replace the subselect with the actual ids 1,2,3,etc and it will.
There is a difference in how the optimizer views certain clauses.
[...] A bit ago ago I posted
[...] A bit ago ago I posted some optimizer hints and recommended using them to write more readable queries. Today I stumbled across an example of my own work that I changed. This is not quite a case of using BETWEEN instead of AND statements, but it sort of is. Basically, I needed to reuse this query: [...]
There are some nice features
There are some nice features about triggers that I think are missing and I'd like to see as future features.
You an read more at MySQL Trigger Features - http://blog.arabx.com.au/?p=337
Scott -- The section of the
Scott -- The section of the manual you're looking at compares =, , , etc.
For instance, if you give the optimizer
WHERE expr=1 OR expr=2
that uses equality. If you give the optimizer
WHERE expr IN (1,2)
what does it use? Well, obviously to us, equality. What it says is that if you use constants instead of functions, etc. then it will go faster. It's intuitive that quality comparisons (like expr=1) go faster.
What the internals manual states is that it doesn't matter how you write it, because they will be processed the same way. If you have a constant in the IN statement, or a constant in each part of the OR, it's faster, no matter what. But there's no difference to the optimizer between using IN() or a bunch of OR statements.
I find using IN() to be more readable than a bunch of OR statements.
The manual also says about
The manual also says about IN ():
"If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants."
(http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html)
I would have thought that meant there is a considerable difference between IN () and =OR=. Does it not?