One TRIGGER Fact, Optimizer Rewriting Stuff

  • One thing about TRIGGERs is that you can have 6 triggers per table:
    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.

  • One thing about TRIGGERs is that you can have 6 triggers per table:
    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.

  • Comments are closed.