SpirirHost Support Centre: MySQL Reference Manual p.351

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

Chapter 5: MySQL Optimisation 351 to allow other threads access to the table. This would still result in a nice performance gain. Of course, LOAD DATA INFILE is much faster for loading data. To get some more speed for both LOAD DATA INFILE and INSERT, enlarge the key buer. See Section 5.5.2 [Server parameters], page 363. 5.2.10 Speed of UPDATE Queries Update queries are optimised as a SELECT query with the additional overhead of a write. The speed of the write is dependent on the size of the data that is being updated and the number of indexes that are updated. Indexes that are not changed will not be updated. Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table. Note that, with dynamic record format, updating a record to a longer total length may split the record. So if you do this often, it is very important to OPTIMIZE TABLE sometimes. See Section 4.5.1 [OPTIMIZE TABLE], page 247. 5.2.11 Speed of DELETE Queries If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. See Section 6.4.7 [TRUNCATE], page 460. The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache. See Section 5.5.2 [Server parameters], page 363. 5.2.12 Other Optimisation Tips Unsorted tips for faster systems: Use persistent connections to the database to avoid the connection overhead. If you can't use persistent connections and you are doing a lot of new connections to the database, you may want to change the value of the thread_cache_size variable. See Section 5.5.2 [Server parameters], page 363. Always check that all your queries really use the indexes you have created in the tables. In MySQL you can do this with the EXPLAIN command. See Section 5.2.1 [Explain], page 338. Try to avoid complex SELECT queries on MyISAM tables that are updated a lot. This is to avoid problems with table locking. The new MyISAM tables can insert rows in a table without deleted rows at the same time another table is reading from it. If this is important for you, you should consider methods where you don't have to delete rows or run OPTIMIZE TABLE after you have deleted a lot of rows.

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