SpirirHost Support Centre: MySQL Reference Manual p.356

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

356 MySQL Technical Reference for Version 4.0.3 Try to get the SELECT statements to run faster. You may have to create some summary tables to do this. Start mysqld with --low-priority-updates. This will give all statements that update (modify) a table lower priority than a SELECT statement. In this case the last SELECT statement in the previous scenario would execute before the INSERT statement. You can give a specic INSERT, UPDATE, or DELETE statement lower priority with the LOW_PRIORITY attribute. Start mysqld with a low value for max write lock count to give READ locks after a certain number of WRITE locks. You can specify that all updates from a specic thread should be done with low priority by using the SQL command: SET LOW_PRIORITY_UPDATES=1. See Section 5.5.6 [SET], page 369. You can specify that a specic SELECT is very important with the HIGH_PRIORITY attribute. See Section 6.4.1 [SELECT], page 447. If you have problems with INSERT combined with SELECT, switch to use the new MyISAM tables as these support concurrent SELECTs and INSERTs. If you mainly mix INSERT and SELECT statements, the DELAYED attribute to INSERT will probably solve your problems. See Section 6.4.3 [INSERT], page 454. If you have problems with SELECT and DELETE, the LIMIT option to DELETE may help. See Section 6.4.6 [DELETE], page 459. 5.4 Optimising Database Structure 5.4.1 Design Choices MySQL keeps row data and index data in separate les. Many (almost all) other databases mix row and index data in the same le. We believe that the MySQL choice is better for a very wide range of modern systems. Another way to store the row data is to keep the information for each column in a separate area (examples are SDBM and Focus). This will cause a performance hit for every query that accesses more than one column. Because this degenerates so quickly when more than one column is accessed, we believe that this model is not good for general purpose databases. The more common case is that the index and data are stored together (like in Oracle/Sybase et al). In this case you will nd the row information at the leaf page of the index. The good thing with this layout is that it, in many cases, depending on how well the index is cached, saves a disk read. The bad things with this layout are: Table scanning is much slower because you have to read through the indexes to get at the data. You can't use only the index table to retrieve data for a query. You lose a lot of space, as you must duplicate indexes from the nodes (as you can't store the row in the nodes).

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