SpirirHost Support Centre: MySQL Reference Manual p.358

<< 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

358 MySQL Technical Reference for Version 4.0.3 5.4.3 How MySQL Uses Indexes Indexes are used to nd rows with a specic value of one column fast. Without an index MySQL has to start with the rst record and then read through the whole table until it nds the relevant rows. The bigger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly get a position to seek to in the middle of the datale without having to look at all the data. If a table has 1000 rows, this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1000 rows it is faster to read sequentially because we then avoid disk seeks. All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored in B-trees. Strings are auto- matically prex- and end-space compressed. See Section 6.5.7 [CREATE INDEX], page 481. Indexes are used to: Quickly nd the rows that match a WHERE clause. Retrieve rows from other tables when performing joins. Find the MAX() or MIN() value for a specic indexed column. This is optimised by a preprocessor that checks if you are using WHERE key part # = constant on all key parts < N. In this case MySQL will do a single key lookup and replace the MIN() expression with a constant. If all expressions are replaced with constants, the query will return at once: SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_ Sort or group a table if the sorting or grouping is done on a leftmost prex of a usable key (for example, ORDER BY key_part_1,key_part_2 ). The key is read in reverse order if all key parts are followed by DESC. See Section 5.2.7 [ORDER BY optimisation], page 347. In some cases a query can be optimised to retrieve values without consulting the datale. If all used columns for some table are numeric and form a leftmost prex for some key, the values may be retrieved from the index tree for greater speed: SELECT key_part3 FROM table_name WHERE key_part1=1 Suppose you issue the following SELECT statement: mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimiser tries to nd the most restrictive index by deciding which index will nd fewer rows and using that index to fetch the rows. If the table has a multiple-column index, any leftmost prex of the index can be used by the optimiser to nd rows. For example, if you have a three-column index on (col1,col2,col3), you have indexed search capabilities on (col1), (col1,col2), and (col1,col2,col3). MySQL can't use a partial index if the columns don't form a leftmost prex of the index. Suppose you have the SELECT statements shown here: mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

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