<< previous page   --   table of contents   --   next page >>
| | | | | | | |
  • Return to Table of Contents
    • Table of Contents
    1. General Information
    2. MySQL Installation
    3. Tutorial Introduction
    4. Database Administration
    5. MySQL Optimisation
    6. MySQL Language Reference
    7. MySQL Table Types
    8. MySQL APIs
    9. Extending MySQL
    Chapter 5:  MySQL Optimisation 347 RIGHT JOIN is implemented analogously as LEFT JOIN. The table read order forced by LEFT JOIN and STRAIGHT JOIN will help the join optimiser (which calculates in which order tables should be joined) to do its work much more quickly, as there are fewer table permutations to check. Note that the above means that if you do a query of type: SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key MySQL will do a full scan on b as the LEFT JOIN will force it to be read before d. The x in this case is to change the query to: SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key) WHERE b.key=d.key 5.2.7  How MySQL Optimises ORDER BY In some cases MySQL can uses index to satisfy an ORDER BY or GROUP BY request without doing any extra sorting. The index can also be used even if the ORDER BY doesn't match the index exactly, as long as all the unused index parts and all the extra are ORDER BY columns are constants in the WHERE  clause.  The following queries will use the index to resolve the  ORDER BY  /  GROUP BY part: SELECT * FROM t1 ORDER BY key_part1,key_part2,... SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2 SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC Some cases where MySQL can not use indexes to resolve the ORDER BY:  (Note that MySQL will still use indexes to nd the rows that matches the WHERE clause):    You are doing an ORDER BY on di erent keys: SELECT * FROM t1 ORDER BY key1,key2    You are doing an ORDER BY using non-consecutive key parts. SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2    You are mixing ASC and DESC. SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC    The key used to fetch the rows are not the same one that is used to do the ORDER BY: SELECT * FROM t1 WHERE key2=constant ORDER BY key1    You are joining many tables and the columns you are doing an ORDER BY on are not all from the rst not-const table that is used to retrieve rows (This is the rst table in the EXPLAIN output which doesn't use a const row fetch method).    You have di erent ORDER BY and GROUP BY expressions.    The used table index is an index type that doesn't store rows in order.  (Like the HASH index in HEAP tables).
     

    Customer Support CentreMySQL Reference Manual

    Web Hosting Services
    UNIX WEB HOSTING
    SUPPORT & FAQ's
    TERMS OF USE
    Domain Services
    DOMAIN REGISTRATION
    MANAGE YOUR ACCOUNT
    SUPPORT & FAQ's
    TERMS OF USE
    SITE MAP
    Home
    Hosting Plans | Domain Registration | About Us | Contact Us | Site Map
    Terms of Use | Privacy Policy | Guarantees
    Merchant Accounts

    SpiritHost - web hosting for spiritual and education sites
    SpiritHit.com - Religious and Spiritual Portal

    Copyright © 2002 Dyntex Group, Inc. All Rights Reserved

  • Return to Table of Contents
    		•
  • Back to top
    • Web Hosting: Manuals & FAQ's
    1. Unix-Based Web Hosting
    2. Unix Dedicated Servers
    3. Windows Dedicated Servers
    4. CuteFTP User’s Guide
    5. CuteHTML User’s Guide
    6. WS_FTP Pro User's Guide
    7. Miva Order User's Guide
    8. Miva Merchant User's Guide