In mysql5.6.19, The subquery cannot use the index. mysql5.6.19 Index

In mysql5.6.19, The subquery cannot use the index. mysql5.6.19 Index

The table structure is simple.

Create table 'oplogs '(
'Id' int (10) unsigned not null AUTO_INCREMENT,
'Kind' varchar (45) not null default '',
'Op' varchar (100) not null,
'User' varchar (25) not null default '',
'IP' varchar (16) not null default '',
'Updatetime' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
Primary key ('id ')
) ENGINE = MyISAM AUTO_INCREMENT = 34896 default charset = utf8 ROW_FORMAT = FIXED

34895 results

The SQL query statement is:

SELECT. id,. kind,. op,. user,. ip,. updatetime FROM oplogs as a inner join (select id from oplogs where 1 order by id desc limit 0, 20) as B using (id)

In the case of 5.6.19

Read and display content in php

It takes 3.5 seconds to display the result for the first time.

But the same configuration

In linux 5.5.38

It takes about 0.7 seconds.

Strange situation

Then, explain is executed under 5.6 and the result is as follows:

The same data explain structure is as follows:

The comparison results are obvious mainly because when oplogs is sorted, the rows row changes too much, and a complete traversal is performed. The first one uses the index, which leads to a large gap, but the cause is not found yet, who knows?

 

MYSQL index question: how to use indexes in queries? After reading a lot of materials, I only want to say whether the index is created or not?

Suppose you have a table,

SQL> CREATE TABLE test_tab (
2 id INT,
3 name VARCHAR (10 ),
4 age INT,
5 val VARCHAR (10)
6 );

Your business has a query, yes
SELECT * FROM test_tab WHERE name = data of an external input

At the beginning, when there was not much data, the execution results were good.
As the amount of data increases, the query becomes slower and slower.

Then the index is created on the name.
Create index idx_test4_name ON test_tab (name );
This will speed up the previous query.

However, one day, you executed the following SQL statement and found that the speed was slow.
SELECT * FROM test_tab WHERE age = 25
Why? Because there is no index on the age Field
The index is only on the name.

In other words, the condition in the WHERE clause will automatically determine whether there are available indexes. If there is any, this should not be used.

A multi-column index is an index that contains two fields.

For example:
Create index idx_test_name_age ON test_tab (name, age );

So
SELECT * FROM test_tab
WHERE
Name LIKE 'zhang %'
AND age = 25
The above indexes can be used for such queries.

Multiple-column indexes are also available. In some cases, it is sufficient to only access the index for queries, and no more table access is required. For example:

SELECT
AVG (avg) AS average age
FROM
Test_tab
WHERE
Name LIKE 'zhang %'

At this time, name and age are included in the index. You do not need to retrieve the data in the table for query.

Indexes cannot be used for multi-table join queries.

Here you need to create several indexes (Foreign keys may also be created ):
1. a. fk1

2. c. fk

3. a. fk2

4. B. id. Check that the primary key already exists.