SpirirHost Support Centre: MySQL Reference Manual p.361

<< 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 361 mysql> SELECT * FROM test WHERE last_name="Widenius"; mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND first_name="Michael"; mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND (first_name="Michael" OR first_name="Monty"); mysql> SELECT * FROM test WHERE last_name="Widenius" -> AND first_name >="M" AND first_name < "N"; However, the name index will NOT be used in the following queries: mysql> SELECT * FROM test WHERE first_name="Michael"; mysql> SELECT * FROM test WHERE last_name="Widenius" -> OR first_name="Michael"; For more information on the manner in which MySQL uses indexes to improve query per- formance, see Section 5.4.3 [MySQL indexes], page 358. 5.4.6 Why So Many Open tables? When you run mysqladmin status, you'll see something like this: Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: This can be somewhat perplexing if you only have 6 tables. MySQL is multi-threaded, so it may have many queries on the same table simultaneously. To minimise the problem with two threads having dierent states on the same le, the table is opened independently by each concurrent thread. This takes some memory but will normaly increase performance. Wth ISAM and MyISAM tables this also requires one extra le descriptor for the datale. With these table types the index le descriptor is shared between all threads. You can read more about this topic in the next section. See Section 5.4.7 [Table cache], page 361. 5.4.7 How MySQL Opens and Closes Tables table_cache, max_connections, and max_tmp_tables aect the maximum number of les the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open le descriptors. However, you can increase the limit on many systems. Consult your OS documentation to nd out how to do this, because the method for changing the limit varies widely from system to system. table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table cache of at least 200 * n, where n is the maximum number of tables in a join. You also need to reserve some extra le descriptors for temporary tables and les.

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