SpirirHost Support Centre: MySQL Reference Manual p.348

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

348 MySQL Technical Reference for Version 4.0.3 The index colum may contain NULL values and one is using ORDER BY ... DESC. This is because in SQL NULL values is always sorted before normal values, independent of you are using DESC or not. In the cases where MySQL have to sort the result, it uses the following algorithm: Read all rows according to key or by table scanning. Rows that doesn't match the WHERE clause are skipped. Store the sort-key in a buer (of size sort_buffer). When the buer gets full, run a qsort on it and store the result in a temporary le. Save a pointer to the sorted block. (In the case where all rows ts into the sort buer, no temporary le is created) Repeat the above until all rows have been read. Do a multi-merge of up to MERGEBUFF (7) regions to one block in another temporary le. Repeat until all blocks from the rst le are in the second le. Repeat the following until there is less than MERGEBUFF2 (15) blocks left. On the last multi-merge, only the pointer to the row (last part of the sort-key) is written to a result le. Now the code in `sql/records.cc' will be used to read through them in sorted order by using the row pointers in the result le. To optimize this, we read in a big block of row pointers, sort these and then we read the rows in the sorted order into a row buer (record_rnd_buffer) . You can with EXPLAIN SELECT ... ORDER BY check if MySQL can use indexes to resolve the query. If you get Using filesort in the extra column, then MySQL can't use indexes to resolve the ORDER BY. See Section 5.2.1 [EXPLAIN], page 338. If you want to have a higher ORDER BY speed, you should rst see if you can get MySQL to use indexes instead of having to do an extra sorting phase. If this is not possible, then you can do: Increase the size of the sort_buffer variable. Increase the size of the record_rnd_buffer variable. Change tmpdir to point to a dedicated disk with lots of empty space. 5.2.8 How MySQL Optimises LIMIT In some cases MySQL will handle the query dierently when you are using LIMIT # and not using HAVING: If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan. If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the rst # lines instead of sorting the whole table. When combining LIMIT # with DISTINCT, MySQL will stop as soon as it nds # unique 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