Общие > Базы данных
Можно ли упорядочить данные?
Tepncuxopa:
Создала. Дальше что? При обзоре (я phpmyadmin использую) все равно записи не упорядочены :(
Chs:
--- Цитировать ---
Затем, что я в другом порядке все равно выводить данные не буду (это точно!). А при запросе теряется время на упорядочивание.
--- Конец цитаты ---
Column indexes
--------------
All *MySQL* column types can be indexed. Use of indexes on the
relevant columns is the best way to improve the performance of `SELECT\'
operations.
A table may have up to 16 indexes. The maximum index length is 256
bytes, although this may be changed when compiling *MySQL*.
For `CHAR\' and `VARCHAR\' columns, you can index a prefix of a column.
This is much faster and requires less disk space than indexing the
whole column. The syntax to use in the `CREATE TABLE\' statement to
index a column prefix looks like this:
KEY index_name (col_name(length))
The example below creates an index for the first 10 characters of the
`name\' column:
mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
For `BLOB\' and `TEXT\' columns, you must index a prefix of the column,
you cannot index the entire thing.
Tepncuxopa:
--- Цитировать ---А для ускорения используйте индексы.
--- Конец цитаты ---
А как это работает?
Chs:
--- Цитировать ---
А как это работает?
--- Конец цитаты ---
How *MySQL* uses indexes
========================
Indexes are used to find rows with a specific value of one column fast.
Without an index *MySQL* has to start with the first record and then
read through the whole table until it finds the relevent rows. The
bigger the table, the more this costs. If the table has an index for
the colums in question, *MySQL* can get fast a position to seek to in
the middle of the data file without having to look at all the data. If
a table has 1000 rows this is at least 100 times faster than reading
sequentially. Note that if you need to access almost all 1000 rows it
is faster to read sequentially because we then avoid disk seeks.
All *MySQL* indexes (`PRIMARY\', `UNIQUE\' and `INDEX\') are stored in
B-trees. Strings are automatically prefix- and end-space compressed.
*Note `CREATE INDEX\': CREATE INDEX.
Indexes are used to:
* Quickly find the rows that match a `WHERE\' clause.
* Retrieve rows from other tables when performing joins.
* Find the `MAX()\' or `MIN()\' value for a specific indexed column.
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
* Sort or group a table if the sorting or grouping is done on a
leftmost prefix of a usable key (e.g., `ORDER BY
key_part_1,key_part_2 \'). The key is read in reverse order if all
key parts are followed by `DESC\'.
The index can also be used even if the `ORDER BY\' doesn\'t match
gthe index exactly, as long as all the not used index parts and
all the extra are `ORDER BY\' columns are constants in the `WHERE\'
clause. The following queries will use the index to resolve the
`ORDER BY\' part.
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3;
SELECT * FROM foo WHERE column=constant ORDER BY column, key_part1;
SELECT * FROM foo WHERE key_part1=const GROUP BY key_part2;
* In some cases a query can be optimized to retrieve values without
consulting the data file. If all used columns for some table are
numeric and form a leftmost prefix for some key, the values may be
retrieved from the index tree for greater speed.
SELECT key_part3 FROM table_name WHERE key_part1=1
Suppose you issue the following `SELECT\' statement:
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
If a multiple-column index exists on `col1\' and `col2\', the appropriate
rows can be fetched directly. If separate single-column indexes exist
on `col1\' and `col2\', the optimizer tries to find the most restrictive
index by deciding which index will find fewer rows and using that index
to fetch the rows.
If the table has a multiple-column index, any leftmost prefix of the
index can be used by the optimizer to find rows. For example, if you
have a three-column index on `(col1,col2,col3)\', you have indexed
search capabilities on `(col1)\', `(col1,col2)\' and `(col1,col2,col3)\'.
*MySQL* can\'t use a partial index if the columns don\'t form a leftmost
prefix of the index. Suppose you have the `SELECT\' statements shown
below:
mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
If an index exists on `(col1,col2,col3)\', only the first query shown
above uses the index. The second and third queries do involve indexed
columns, but `(col2)\' and `(col2,col3)\' are not leftmost prefixes of
`(col1,col2,col3)\'.
*MySQL* also uses indexes for `LIKE\' comparisons if the argument to
`LIKE\' is a constant string that doesn\'t start with a wildcard
character. For example, the following `SELECT\' statements use indexes:
mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";
In the first statement, only rows with `"Patrick" =\', `
Tepncuxopa:
Спасибо
Навигация
Перейти к полной версии