Dyntex.net Support Centre: MySQL Reference Manual p.349

<< 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 349 In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BYs. As soon as MySQL has sent the rst # rows to the client, it will abort the query (If you are not using SQL_CALC_FOUND_ROWS). LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns. When the server uses temporary tables to resolve the query, the LIMIT # is used to calculate how much space is required. 5.2.9 Speed of INSERT Queries The time to insert a record consists approximately of: Connect: (3) Sending query to server: (2) Parsing query: (2) Inserting record: (1 x size of record) Inserting indexes: (1 x number of indexes) Close: (1) where the numbers are somewhat proportional to the overall time. This does not take into consideration the initial overhead to open tables (which is done once for each concurrently running query). The size of the table slows down the insertion of indexes by N log N (B-trees). Some ways to speed up inserts: If you are inserting many rows from the same client at the same time, use multiple value lists INSERT statements. This is much faster (many times in some cases) than using separate INSERT statements. If you are adding data to non-empty table, you may tune up bulk_insert_buffer_size variable to make it even faster. See Section 4.5.6.4 [SHOW VARIABLES], page 257. If you are inserting a lot of rows from dierent clients, you can get higher speed by using the INSERT DELAYED statement. See Section 6.4.3 [INSERT], page 454. Note that with MyISAM tables you can insert rows at the same time SELECTs are running if there are no deleted rows in the tables. When loading a table from a text le, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements. See Section 6.4.9 [LOAD DATA], page 461. It is possible with some extra work to make LOAD DATA INFILE run even faster when the table has many indexes. Use the following procedure: 1. Optionally create the table with CREATE TABLE. For example, using mysql or Perl- DBI. 2. Execute a FLUSH TABLES statement or the shell command mysqladmin flush- tables.

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

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