SpirirHost Support Centre: MySQL Reference Manual p.338

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

338 MySQL Technical Reference for Version 4.0.3 name suggests, it can bring your system down to its knees if you ask it, so make sure to use it only on your development systems. 5.2 Optimising SELECTs and Other Queries First, one thing that aects all queries: The more complex permission system setup you have, the more overhead you get. If you do not have any GRANT statements done, MySQL will optimise the permission checking somewhat. So if you have a very high volume it may be worth the time to avoid grants. Otherwise, more permission check results in a larger overhead. If your problem is with some explicit MySQL function, you can always time this in the MySQL client: mysql> SELECT BENCHMARK(1000000,1+1); +------------------------+ | BENCHMARK(1000000,1+1) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.32 sec) The above shows that MySQL can execute 1,000,000 + expressions in 0.32 seconds on a PentiumII 400MHz. All MySQL functions should be very optimised, but there may be some exceptions, and the BENCHMARK(loop_count,expression) is a great tool to nd out if this is a problem with your query. 5.2.1 EXPLAIN Syntax (Get Information About a SELECT) EXPLAIN tbl_name or EXPLAIN SELECT select_options EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name. When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order. With the help of EXPLAIN, you can see when you must add indexes to tables to get a faster SELECT that uses indexes to nd the records. You can also see if the optimiser joins the tables in an optimal order. To force the optimiser to use a specic join order for a SELECT statement, add a STRAIGHT_JOIN clause. For non-simple joins, EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the order they would be read. MySQL resolves all joins using a single-sweep multi-join method. This means that MySQL reads a row from the rst table, then nds a matching row in the second table, then in the third table and so on. When all tables are processed, it outputs the selected columns and backtracks through the

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