,
nike lebron
| Back to logs list
326378 2010 年 12 月 09 日 14:33 Reading (loading. ..) Comments (0) Category: SQL database
any database programmer will have such an experience: high-traffic database-driven program, a bad SQL query on the entire application could have serious implications for the operation, which not only consume more database time ,
lebron james shoes, and it will affect other application components.
as other subjects, optimizing query performance depends very much on the developer's intuition. Fortunately, as the MySQL database that comes with some assistance tools. This article briefly discusses the various tools of three ways: using an index, use the MySQL EXPLAIN of query and adjust the internal configuration.
1: Use the index
MySQL database tables to allow for indexing, in order to find records quickly without having to start scanning the entire table, thus significantly speed up queries. Each table can be up to 16 index, MySQL also supports multi-column index and full-text search.
add an index to the table is very simple, just call a CREATE INDEX command for index to specify its domain. A list gives an example:
List A
mysql> CREATE INDEX idx_username ON users (username);
Query OK, 1 row affected (0.15 sec)
Records: 1 Duplicates: 0 Warnings: 0
here, the username of the domain users to index the table to ensure the WHERE or HAVING clause in reference to the field SELECT query run faster than the index did not add faster. By SHOW INDEX command to view the index has been created (list B).
list B
mysql> SHOW INDEX FROM users;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
| users | 1 | idx_username | 1 | username | A | NULL | NULL | NULL | YES | BTREE | |
1 row in set (0.00 sec)
is worth noting that: the index is like a double-edged sword. Each table is usually not necessary to index the domain, and is likely to lead to slower speed, because the table insert or modify data, MySQL had each of these additional work re-indexing. On the other hand, to avoid the index table to do the same for each domain is not a very good idea,
zoom kobe v, because the inserted record in improving the speed, resulting in slower query operation. This need to find a balance, such as in the design of index system, consider the following main functions (data recovery and editing) may well be a wise choice.
2: optimize query performance
query performance in the analysis, consider the EXPLAIN keyword is equally useful. General keyword EXPLAIN SELECT query on the front, used to describe how to perform query MySQL and MySQL to return a result set to perform successfully the number of rows. Here's a simple example to explain (list C) in this process:
list C
mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4079 | Using where |
2 rows in set (0.00 sec) This query is based on two table join. MySQL is the EXPLAIN keyword describes how to connect the two tables. Must be clear,
lebron shoes, the current design MySQL deal is a record in the country table and the city table records the entire 4019. This means that also use other optimization techniques to improve its query. For example, to the city to add the following index table (list D):
list D
mysql> CREATE INDEX idx_ccode ON city (countrycode);
Query OK, 4079 rows affected (0.15 sec)
Records: 4079 Duplicates: 0 Warnings: 0
Now, when we re-use the EXPLAIN keyword query, we can see a significant improvement (List E):
list E
The following is quoted fragment:
mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index |
| 1 | SIMPLE | city | ref | idx_ccode | idx_ccode | 3 | const | 333 | Using where |
2 rows in set (0.01 sec)
In this case,
nike kobe v, MySQL now only need to scan the city record in the table 333 can produce a result set,
lebron james vii, the scanner records a reduction of almost 90%! natural resources, query the database faster, more efficient .
BEVERLY HILLS POLO CLUB Mens Air Force 1 Court Sneakers ... - Blogger
explosively
Linear Mode
