SpirirHost Support Centre: MySQL Reference Manual p.344

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

344 MySQL Technical Reference for Version 4.0.3 slower while the table gets bigger. After the data gets too big to be cached, things will start to go much slower until your applications is only bound by disk-seeks (which increase by N log N). To avoid this, increase the index cache as the data grows. See Section 5.5.2 [Server parameters], page 363. 5.2.3 Speed of SELECT Queries In general, when you want to make a slow SELECT ... WHERE faster, the rst thing to check is whether you can add an index. See Section 5.4.3 [MySQL indexes], page 358. All references between dierent tables should usually be done with indexes. You can use the EXPLAIN command to determine which indexes are used for a SELECT. See Section 5.2.1 [EXPLAIN], page 338. Some general tips: To help MySQL optimise queries better, run myisamchk --analyze on a table after it has been loaded with relevant data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1, of course.) MySQL will use this to decide which index to choose when you connect two tables with 'a non-constant expression'. You can check the result from the analyze run by doing SHOW INDEX FROM table_name and examining the Cardinality column. To sort an index and data according to an index, use myisamchk --sort-index -- sort-records=1 (if you want to sort on index 1). If you have a unique index from which you want to read all records in order according to that index, this is a good way to make that faster. Note, however, that this sorting isn't written optimally and will take a long time for a large table! 5.2.4 How MySQL Optimises WHERE Clauses The WHERE optimisations are put in the SELECT part here because they are mostly used with SELECT, but the same optimisations apply for WHERE in DELETE and UPDATE statements. Also note that this section is incomplete. MySQL does many optimisations, and we have not had time to document them all. Some of the optimisations performed by MySQL are listed here: Removal of unnecessary parentheses: ((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d) Constant folding: (a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5 Constant condition removal (needed because of constant folding): (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6

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