Task
Table scan is too slow. Or inserting and updating records takes too long.

Solution
The ALTER TABLE clause can remove and add not only columns, but also indexes on those columns. Such operations often improve database performance. Typically, indexing a frequently used column speeds up SELECT statements by not having to scan the tables completely. In some cases, deleting the index can also be beneficial. Whenever a row is updated, MySQL must update all indexes containing the changed columns. If you rarely use an index, this may indicate that the table is overloaded with indexes, and removing one of them may improve the efficiency of table processing.

Discussion
For ease of use, let's start by creating a new instance of the mytbl test table. We use the DROP TABLE and CREATE TABLE clauses to drop the existing version and recreate the table in its original form:

DROP TABLE mytbl;
CREATE TABLE mytbl
i INT,
c CHAR(1)
);

At the beginning of the chapter, we used SHOW COLUMNS to monitor the results of changing a table.

Now we will examine index changes and display the results using SHOW INDEX rather than SHOW COLUMNS. There are currently no indexes on the table because they were not specified in the CREATE TABLE statement:

Mysql> SHOW INDEX FROM mytbl;
Empty set (0.00 sec)

Adding Indexes
There are four types of clauses that add indexes to a table:

ALTER TABLE table_name ADD PRIMARY KEY (column_list);
ALTER TABLE table_name ADD UNIQUE index_name (column_list);
ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD FULLTEXT index_name (column_list);

The first clause adds a primary key (PRIMARY KEY), that is, the indexed values must be unique and not contain NULL. The second clause creates an index for which the values must be unique (except for NULL values, which can occur multiple times). The third clause adds a regular index in which any value can appear multiple times. The latter creates a special FULLTEXT index, which is used to view text.

If there is an index_name in sentence constructions, then it is optional.

If you do not specify it, MySQL will automatically assign a name to the index.

The columns to be indexed are specified in the column_list parameter—a list of one or more column names separated by commas.

Let's look at two simple examples: the first creates a single-column index on c, and the second creates a multi-column index involving c and i:

ALTER TABLE mytbl ADD INDEX (c);
ALTER TABLE mytbl ADD INDEX (c,i);

In many cases, the columns being indexed must be declared non-NULL. For example, if you create mytbl as an ISAM table, the ADD INDEX statements above will fail because ISAM tables do not allow NULL in any index types. Additionally, PRIMARY KEY indexes cannot contain NULL values, regardless of the table type. If you are trying to add an index and MySQL complains about NULL issues, use the ALTER TABLE clause to change the relevant column(s) to non-NULL and try creating the index again. For example, if you try to make column i the primary key, you will get an error:

Mysql>
ERROR 1171 at line 5: All parts of a PRIMARY KEY must be NOT NULL;
If you need NULL in a key, use UNIQUE instead

You must first redefine the i column so that it does not allow NULL:

Mysql> ALTER TABLE mytbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE mytbl ADD PRIMARY KEY (i);

Everything worked out.

And in the first case, as can be seen from the error message, instead of the primary key, you could create a UNIQUE index if you need NULL values in the index. Removing indexes To delete an index, use one of the following sentences:

ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE table_name DROP INDEX index_name;

The easiest way to delete a PRIMARY KEY index is because you don't need to know the index name:

ALTER TABLE mytbl DROP PRIMARY KEY;

To delete an index that is not a primary key, you must specify its name. If you don't know what the index is called, use SHOW INDEX. To avoid outputting too long lines, use vertical output (\G):

Mysql> SHOW INDEX FROM mytbl\G
************************** 1. row ******************** *******
Table: mytbl
Non_unique: 1
Key_name: c
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:
************************** 2. row ******************** *******
Table: mytblNon_unique: 1
Key_name: c_2
Seq_in_index: 1
Column_name: c
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:
************************** 3. row ******************** *******
Table: mytbl
Non_unique: 1
Key_name: c_2
Seq_in_index: 2
Column_name: i
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Comment:

The Key_name and Seq_in_index values correspond to the names of the indexes and the positions of the columns in the index. Now you know that the mytbl table has a single-column index named c and a multi-column index named c_2 (these names were chosen by MySQL for the two indexes we created earlier). The statement removing indexes would be:

ALTER TABLE mytbl DROP INDEX c, DROP INDEX c_2;

As you can see, multiple operations can be performed in one ALTER TABLE statement, which must be separated by commas.

Table indexing is used to reduce data search time. Without indexes, the search occurs by simply enumerating all the table values, which takes quite a long time.

The following types of indexes are used in mysql tables:

Primary key
Unique index
Regular index
Full text index

In addition to the above indexes, it is also used full text index, but it is not discussed in this article.

Primary key

The primary key is used to uniquely identify records in a table. Among the main points regarding the primary key, the following should be noted:

1. a table cannot have only one unique key;

2. the key value must be unique within the table;

3. The primary key cannot be of type NULL.

Example of creating a primary key:

Creating a primary key when creating a table in the field description

Example 3:

If the primary key is a text field, then you need to indicate in parentheses the number of characters involved in indexing.

Example 5:

If the table has already been created, then you can add a primary index as follows.

$query = "ALTER table auto ADD CONSTRAINT PRIMARY KEY (id_number)";
$result = $connection->query($query);
?>

Regular and unique indexes

In addition to the primary index, a table can also contain regular and unique indexes. Unlike the primary index, there may be several of these indexes. The difference between a regular index and a unique index is that a unique index cannot have identical values.

Declaring regular indexes is done using keywords key or index. If you need to declare a unique index, then before key or index the keyword is put unique.

Example 6:

Create a unique index

Removing indexes

Removing indexes is carried out using the drop index command; using it, any indexes are deleted.

Example 8:

$connection = new mysqli("localhost","root","","mybase");
$query = "drop index model on auto";
$result = $connection->query($query);
?>

footer in word 2007, 0000002111

Using indexes in MySQL

Indexes are used to quickly find rows with a specified value in one column. Without an index, a table is read through the entire table, starting with the first record, until matching rows are found. The larger the table, the greater the overhead. If the table contains an index on the columns in question, then MySQL can quickly determine the search position in the middle of the data file without going through all the data. For a table containing 1000 rows, this will be at least 100 times faster compared to iterating through all the records sequentially. However, in the case where almost all 1000 rows need to be accessed, sequential reads will be faster because there are no disk seeks required.

All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored as B-trees. Strings are automatically compressed, removing prefix and trailing spaces.

Indexes are used to:

Quickly find rows that match a WHERE clause.

Retrieve rows from other tables when performing joins.

Find the MAX() or MIN() values for a given indexed column. This operation is optimized by the preprocessor, which checks that you are not using WHERE key_part_4 = constant, over all parts of the composite key< N . В этом случае MySQL сделает один просмотр ключа и заменит выражение константой MIN() . Если все выражения заменяются константой, запрос моментально вернет результат:

SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

Perform sorting or grouping in a table if these operations are performed on the leftmost prefix of the key used (for example, ORDER BY key_part_1,key_part_2). If all parts of a key are followed by DESC , then the key is read in reverse order

In some cases, the query can be optimized to retrieve values without accessing the data file. If all of the used columns in some table are numeric and form the leftmost prefix for some key, then to provide greater speed, the values to be looked up can be retrieved directly from the index tree:

SELECT key_part3 FROM table_name WHERE key_part1=1

Suppose the following SELECT statement is called:

Mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multi-column index exists on columns col1 and col2, then the corresponding rows can be selected directly. In the case where separate indexes exist on columns col1 and col2, the optimizer tries to find the most restrictive index by determining which index finds the fewest rows and uses that index to fetch those rows.

If a given table has a multicolumn index, then any leftmost prefix of that index can be used by the optimizer to find rows. For example, if you have an index on three columns (col1,col2,col3), then there is the potential for indexed searches on (col1), (col1,col2), and (col1,col2,col3).

In MySQL, you cannot use a partial index unless the columns form the leftmost prefix of that index. Let's assume you have the SELECT commands 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), then only the first query shown above uses that index. The second and third queries do include indexed columns, but (col2) and (col2,col3) are not the leftmost part of the (col1,col2,col3) prefixes.

MySQL also uses indexes for LIKE comparisons if the argument in the LIKE expression is a constant string that does not begin with a wildcard character. For example, the following SELECT commands use indexes:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

The first command only looks at lines with "Patrick"<= key_col < "Patricl" , а во второй - только строки с "Pat" <= key_col < "Pau" .

The following SELECT commands will not use indexes:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first command, the LIKE value begins with a wildcard character. In the second command, the LIKE value is not a constant.

MySQL 4.0 introduces a different optimization on the LIKE expression. If the expression... LIKE "%string%" is used and the length of the string is greater than 3 characters, then MySQL will use the Turbo Boyer-Moore algorithm to initialize a pattern for the string and then use that pattern to perform the search faster.

When searching using column_name IS NULL, indexes will be used if column_name is an index.

MySQL usually uses the index that finds the fewest rows. The index is used on columns that are compared using the following operators: =, >, >=,<, <=, BETWEEN и LIKE с префиксом, не содержащим шаблонного символа, такого как something% .

If an index does not cover all AND levels in the WHERE clause, then it is not used to optimize the query. In other words: for an index to be usable, that index's prefix must appear in each AND group.

The following WHERE clauses use indexes:

WHERE index_part1=1 AND index_part2=2 AND other_column=3 ... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */ ... WHERE index_part1="hello" AND index_part_3= 5 /* optimized as "index_part1="hello"" */ ... WHERE index1=1 and index2=2 or index1=3 and index3=3; /* You can use an index at index1, but not at index2 or index 3 */

The following WHERE clauses Not use indexes:

WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */ ... WHERE index=1 OR A=10 /* Index is not used in both parts of the AND */ ... WHERE index_part1=1 OR index_part2=10 /* There is no index covering all rows*/

In some cases, MySQL does not use an index even though it is possible. Some examples of such situations are given below:

If using an index requires MySQL to traverse more than 30% of the rows in a given table (in such cases, table traversal will likely be much faster since fewer lookups will be required). Note that if a query like this uses LIMIT on only the subset of rows being retrieved, then MySQL will use the index anyway, since a small number of rows can be found much faster to return the result.

Column indexes

Columns of all types can be indexed in MySQL.

The maximum number of keys and the maximum length of indexes are determined by the table engine. You can have at least 16 keys across all table engines and a total index length of at least 256 bytes.

For CHAR and VARCHAR columns, you can index the column prefix. This is much faster and requires less disk space than indexing the entire column. The syntax used in the CREATE TABLE command to index a column prefix is something like this:

KEY index_name (col_name(length))

The following example creates an index on the first 10 characters in the name column:

Mysql> CREATE TABLE test ( -> name CHAR(200) NOT NULL, -> KEY index_name(name(10)));

For BLOB and TEXT columns, the column prefix must be indexed. You cannot index an entire column.

In MySQL version 3.23.23 and later, you can also create custom FULLTEXT indexes. They are used for full text search. FULLTEXT full-text indexes only support MyISAM tables. They can only be created on VARCHAR and TEXT columns.

Indexing is always performed on the entire column, and partial indexing is not supported.

Multi-column indexes

MySQL can create indexes on multiple columns. An index can include up to 15 columns (on CHAR and VARCHAR columns you can also use the column prefix as part of the index)

A multi-column index can be thought of as an ordered array containing values created by concatenating the values of the indexed columns.

MySQL uses multi-column indexes in such a way that queries run quickly when you specify the known part for the first column in the index in the WHERE clause, even if you do not specify values for the other columns.

Mysql> CREATE TABLE test ( -> Let's say the following table is created: -> id INT NOT NULL, -> last_name CHAR(30) NOT NULL, -> first_name CHAR(30) NOT NULL, -> PRIMARY KEY (id),

INDEX name (last_name,first_name));

Mysql> The name index is an index on the last_name and first_name columns. This index will be used for queries that specify values in the known region for last_name or for both the last_name and first_name columns. Thus, the name index will be used in the following queries: SELECT * FROM test WHERE last_name="Widenius"; mysql> -> mysql> AND first_name="Michael"; -> SELECT * FROM test WHERE last_name="Widenius" mysql> AND first_name="Michael"; -> AND (first_name="Michael" OR first_name="Monty");< "N";

For more information on how MySQL uses indexes to improve query performance,

Opening and closing tables in MySQL

The table_cache, max_connections, and max_tmp_tables parameters specify the maximum number of files that the server keeps open. If you increase one or both of these parameters, you may encounter the operating system's limit on the number of open file descriptors per process. In many systems, however, this limit can be increased. Because how to change this value may vary from system to system, you should consult your operating system documentation for each specific case.

The table_cache and max_connections values are related. For example, for 200 concurrent connections, you must have a table cache of at least 200 * n, where n is the maximum number of related tables. You also need to reserve a few additional file descriptors for temporary tables and files.

You should make sure that your operating system can handle the number of open file descriptors that this table_cache setting assumes. If table_cache is set too high, MySQL can run out of file descriptors, abort the connection, fail queries, and become very unreliable. You should also take into account that the MyISAM table engine requires two file descriptors for each unique open table. The number of file descriptors allowed by MySQL can be increased using the --open-files-limit=# startup option.

The maximum number of tables in the open table cache will be the number specified in table_cache (default is 64; this number can be changed using the -O table_cache=# option for mysqld). Please note that MySQL may temporarily open more tables to execute queries.

An unused table is closed and removed from the table cache in the following situations:

When the cache is full and the thread tries to open a table that is not in this cache.

When the cache contains more than table_cache input values and the thread is no longer using the table.

When someone runs mysqladmin refresh or mysqladmin flush-tables .

When someone executes FLUSH TABLES .

When the table cache becomes full, the server uses the following procedure to allocate cache inputs for use:

Tables that are not currently in use are freed in order of oldest use.

If the cache is full and no table can be freed and a new table needs to be opened, the cache is temporarily expanded as much as necessary.

If the cache is in a temporarily extended state and a table transitions from a used to an unused state, the table is closed and freed from the cache.

The table is opened for each simultaneous access. This means that if there are two threads accessing the same table, or the same table is accessed twice in the same query (using AS), then that table must be opened twice. Opening any table for the first time requires two file descriptors; for each additional use - only one. An additional first-open handle is used for the index file; this handle is shared by all threads.

When you open a table with the HANDLER table_name OPEN command, a dedicated table object is created for that thread. This table object is not accessible to other threads and will not be closed until the thread issues the HANDLER table_name CLOSE command or the thread itself is killed. If this happens, the table is placed back into the table cache (if it is not full).

To see if the table cache is too small, you should check the Opened_tables variable. If the value is large enough, even if you have not run the FLUSH TABLES command very often, then you need to increase this table cache.

headers and footers in word 2007, 000000211111

All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored as B-trees. Strings are automatically compressed, removing prefix and trailing spaces (see section 6.5.7 CREATE INDEX Statement Syntax).

Indexes are used to:

Quickly find rows that match a WHERE clause.
Retrieve rows from other tables when performing joins.
Find the MAX() or MIN() values for a given indexed column. This operation is optimized by a preprocessor that checks to see if you are using WHERE key_part_4 = constant, over all parts of the composite key . In this case, MySQL will do one lookup on the key and replace the expression with the MIN() constant. If all expressions are replaced with a constant, the query will immediately return the result: SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
Perform sorting or grouping on a table if these operations are performed on the leftmost prefix of the key being used (for example ORDER BY key_part_1,key_part_2). If all parts of a key are followed by DESC , then the key is read in reverse order (see section 5.2.7 How MySQL Optimizes ORDER BY ).
In some cases, the query can be optimized to retrieve values without accessing the data file. If all the used columns in a certain table are numeric and form the leftmost prefix for a certain key, then to provide greater speed, the searched values can be retrieved directly from the index tree: SELECT key_part3 FROM table_name WHERE key_part1=1

Suppose the following SELECT statement is called:

Mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

In MySQL, you cannot use a partial index unless the columns form the leftmost prefix of that index. Let's assume you have the SELECT commands 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;

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

The first command only looks at lines with "Patrick" , and in the second - only lines with "Pat" .

The following SELECT commands will not use indexes:

Mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%"; mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first command, the LIKE value begins with a wildcard character. In the second command, the LIKE value is not a constant.

MySQL 4.0 introduces a different optimization on the LIKE expression. If the expression ... LIKE "%string%" is used and the length of the string is greater than 3 characters, then MySQL will use the Turbo Boyer-Moore algorithm to initialize a pattern for the string and then use that pattern to perform the search faster.

When searching using column_name IS NULL, indexes will be used if column_name is an index.

MySQL usually uses the index that finds the fewest rows. An index is used on columns that are compared using the following operators: =, >, >=, and LIKE with a prefix that does not contain a wildcard character such as something% .

If an index does not cover all AND levels in the WHERE clause, then it is not used to optimize the query. In other words: for an index to be usable, that index's prefix must appear in each AND group.

The following WHERE clauses use indexes:

The following WHERE clauses Not use indexes:

In some cases, MySQL does not use an index even though it is possible. Some examples of such situations are given below:

If using an index requires MySQL to traverse more than 30% of the rows in a given table (in such cases, table traversal will likely be much faster since fewer lookups will be required). Note that if a query like this uses LIMIT on only the subset of rows being retrieved, then MySQL will use the index anyway, since a small number of rows can be found much faster to return the result.
If the index change range can contain NULL values when using ORDER BY ... DESC expressions.

If you have any other questions or something is not clear - welcome to our

In previous articles I often mentioned indexes in MySQL. and I promised that I would tell you about them soon. So, this time has come, and today you will learn about MySQL indexes, about their purpose and how to create them.

Indexes are used to speed up data retrieval from database tables. In fact, index in MySQL- this is sorting a specific field in a table. That is, if a field is made an index, then the entire table will be sorted by this field. Why is this beneficial?

Let's say our table contains 1000000 records. Each entry has a unique identifier ID. And let's say we need to pull out a record from ID = 530124. If there is no index, then MySQL will iterate through all the records in the table until it finds the one it needs. In the worst case, he will be forced to sort out 1000000 records. Of course, this will be a very long time. And if there was an index (that is, the field would be sorted), then the record would be sampled on average in 100,000 times faster. As you can see, the benefits are obvious.

However, indexes have one significant flaw, which does not allow each table field to be an index. In fact, an index is another table, but simply with the corresponding field sorted. That is, by making an index on one field, you create another exactly the same table, which will take up additional disk space.

Another small minus indexes in MySQL The problem is that requests to insert new records force the table to be sorted again. As a result, inserting new records will take a little longer than usual. But do not forget that in most cases you have to do this much less frequently than sampling, so this minus is not significant.

How to make an index in MySQL?

For primary keys ( PRIMARY KEY) the index is created automatically, but for other fields the sequence of actions is PHPMyAdmin next:

And finally, I would like to make a short summary so that you understand: " When to create MySQL indexes":

If a field is sampled very often, then it should be made an index.
If records are added to the table very often, and sampling occurs rarely (this sometimes happens), then there is no need to create indexes.

And one more thing. If suddenly you see that your sampling requests are very slow, then analyze the reason for this. Most likely, you just need to add an index. In general, test it and everything will become clear.