SpirirHost Support Centre: MySQL Reference Manual p.346

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

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

346 MySQL Technical Reference for Version 4.0.3 mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; mysql> SELECT COUNT(*) FROM tbl_name -> WHERE key_part1=val1 AND key_part2=val2; mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1; The following queries use indexing to retrieve the rows in sorted order without a separate sorting pass: mysql> SELECT ... FROM tbl_name -> ORDER BY key_part1,key_part2,... ; mysql> SELECT ... FROM tbl_name -> ORDER BY key_part1 DESC,key_part2 DESC,... ; 5.2.5 How MySQL Optimises DISTINCT DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table. When combining LIMIT # with DISTINCT, MySQL will stop as soon as it nds # unique rows. If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the rst match. SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a; In the case, assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the rst row in t2 is found. 5.2.6 How MySQL Optimises LEFT JOIN and RIGHT JOIN A LEFT JOIN B in MySQL is implemented as follows: The table B is set to be dependent on table A and all tables that A is dependent on. The table A is set to be dependent on all tables (except B) that are used in the LEFT JOIN condition. All LEFT JOIN conditions are moved to the WHERE clause. All standard join optimisations are done, with the exception that a table is always read after all tables it is dependent on. If there is a circular dependence then MySQL will issue an error. All standard WHERE optimisations are done. If there is a row in A that matches the WHERE clause, but there wasn't any row in B that matched the LEFT JOIN condition, then an extra B row is generated with all columns set to NULL. If you use LEFT JOIN to nd rows that don't exist in some table and you have the fol- lowing test: column_name IS NULL in the WHERE part, where column name is a column that is declared as NOT NULL, then MySQL will stop searching after more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.

MySQL Reference Manual

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