SpirirHost Support Centre: MySQL Reference Manual p.360

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

360 MySQL Technical Reference for Version 4.0.3 5.4.4 Column Indexes All MySQL column types can be indexed. Use of indexes on the relevant columns is the best way to improve the performance of SELECT operations. The maximum number of keys and the maximum index length is dened per table handler. See Chapter 7 [Table types], page 494. You can with all table handlers have at least 16 keys and a total index length of at least 256 bytes. For CHAR and VARCHAR columns, you can index a prex of a column. This is much faster and requires less disk space than indexing the whole column. The syntax to use in the CREATE TABLE statement to index a column prex looks like this: KEY index_name (col_name(length)) The example here creates an index for the rst 10 characters of the name column: mysql> CREATE TABLE test ( -> name CHAR(200) NOT NULL, -> KEY index_name (name(10))); For BLOB and TEXT columns, you must index a prex of the column. You cannot index the entire column. In MySQL Version 3.23.23 or later, you can also create special FULLTEXT indexes. They are used for full-text search. Only the MyISAM table type supports FULLTEXT indexes. They can be created only from VARCHAR and TEXT columns. Indexing always happens over the entire column and partial indexing is not supported. See Section 6.8 [Fulltext Search], page 485 for details. 5.4.5 Multiple-Column Indexes MySQL can create indexes on multiple columns. An index may consist of up to 15 columns. (On CHAR and VARCHAR columns you can also use a prex of the column as a part of an index.) A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns. MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the rst column of the index in a WHERE clause, even if you don't specify values for the other columns. Suppose a table is created using the following specication: mysql> CREATE TABLE test ( -> id INT NOT NULL, -> last_name CHAR(30) NOT NULL, -> first_name CHAR(30) NOT NULL, -> PRIMARY KEY (id), -> INDEX name (last_name,first_name)); Then the index name is an index over last_name and first_name. The index will be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index will be used in the following queries:

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