When mysql adds an index, the query becomes slower.

1. Table structure, time1 adds a common index: 

CREATE TABLE `time_table` (
    `time1` INT(11) NULL DEFAULT NULL,
    `time2` INT(11) NULL DEFAULT NULL,
    INDEX `time1` (`time1`)
)
ENGINE=MyISAM
;

2. Worm replication creates 2 million pieces of data 

insert into time_table select * from time_table

3. Start query: time1 has index, time2 has no index, data 2 million 

set profiling=1;
select * from time_table where time2=1111111;
select * from time_table where time1=1111111;
show profiles;

4. As a result, indexing slows down:

< H2 > clipboard.png

400200time1=11111,time2=11111,200time1=22222,time2=22222,

200400time2=1time1()update time2=time1,
time1:

clipboard.png

:

clipboard.png

:

clipboard.png

:
8001111112222

clipboard.png
add indexed fields, but the query is slower.

my guess: the data is the same, the effect of the index is almost no, but because of the index, the field storage space is more, the query is slow, pure wishful thinking, hope to be able to answer one or two! 

qq:2210170490
Mysql
Oct.15,2021

according to the data of the landlord, I also created 4 million data:

< table > < thead > < tr > < th > mysql > select * from index_test limit 5; < / th > < / tr > < / thead > < tbody > < tr > < td > id1 < / td > < td > id2 < / td > < / tr > < tr > < td > 11111 < / td > < td > 11111 < / td > < / tr > < tr > < td > 22222 < / td > < td > 22222 < / td > < / tr > < tr > < td > 11111 < / td > < td > 11111 < / td > < / tr > < tr > < td > 22222 < / td > < td > 22222 < / td > < / tr > < tr > < td > 11111 < / td > < td > 11111 < / td > < / tr > < / tbody > < / table >

id1 create index
execute the id2 predicate condition faster:
mysql > select * from index_test where id1=11111;
2097152 rows in set (3.00 sec)
mysql > select * from index_test where id2=11111;
2097152 rows in set (2.32 sec)
id1's profile is as follows:
+-+-+
| Status | Duration |
+-+
| starting | 0.000080 |
| checking permissions | 0.000014 | |
| Opening tables | 0.000024 |
| init | 0.000033 |
| System lock | 0.000015 |
| optimizing | 0.000018 |
| statistics | 0.035408 |
| preparing | 0.000033 |
| executing | 0.000007 |
| Sending data | 2.963681 |
| end | 0.000021 |
| query end | | | 0.000015 |
| closing tables | 0.000020 |
| freeing items | 0.003474 |
| logging slow query | 0.000117 |
| cleaning up | 0.000072 |
+-+

| < table > < thead > < tr > < th > id2's profile goes like this: < / th > < / tr > < / thead > < tbody > < tr > < td > Status < / td > < td > Duration < / td > < / tr > < tr > < td > starting < / td > < td > 0.000074 < / td > < / tr > < tr > < td > checking permissions < / td > < td > 0.000012 < / td > < / tr > < tr > < td > Opening tables < / td > < td > 0.000025 < / td > < / tr > < tr > < td > init < / td > < td > 0.000032 < / td > < / tr > < tr > < td > System lock < / td > < td > 0.000014 < / td > < / tr > < tr > < td > optimizing < / td > < td > 0.000018 < / td > < / tr > < tr > < td > statistics < / td > < td > 0.000025 < / td > < / tr > < tr > < td > preparing < / td > < td > 0.000019 < / td > < / tr > < tr > < td > executing < / td > < td > 0.000006 < / td > < / tr > < tr > < td > Sending data < / td > < td > 2.318096 < / td > < / tr > < tr > < td > end < / td > < td > 0.000020 < / td > < / tr > < tr > < td > query end < / td > < td > 0.000034 < / td > < / tr > < tr > < td > closing tables < / td > < td > 0.000022 < / td > < / tr > < tr > < td > freeing items < / td > < td > 0.004327 < / td > < / tr > < tr > < td > logging slow query < / td > < td > 0.000093 < / td > < / tr > < tr > < td > cleaning up < / td > < td > 0.000060 < / td > < / tr > < / tbody > < / table >

you can see that the relatively significant time consuming of id1 is that statistics,Sending data, is mainly concentrated in Sending data.
Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client.
that is, id1=11111 spends more time reading data than id2=11111. On the id1, there is a secondary index, and there is a cost of returning to the table when the index is used. In this case, the selectivity of the index is too poor because of the small data base. In this case, the index should not be used.
if you have to use an index, avoid going back to the table and create an overlay index.
alter table index add index com_idx (id1,id2);
Test results show that id1=11111 is faster than id2=11111:
mysql > select * from index_test where id1=11111;
2097152 rows in set (1.71sec)
mysql > select * from index_test where id2=11111;
2097152 rows in set (2.57sec)
the corresponding profile is as follows:

< table > < thead > < tr > < th > id1=11111 < / th > < / tr > < / thead > < tbody > < tr > < td > Status < / td > < td > Duration < / td > < / tr > < tr > < td > starting < / td > < td > 0.000149 < / td > < / tr > < tr > < td > checking permissions < / td > < td > 0.000084 < / td > < / tr > < tr > < td > Opening tables < / td > < td > 0.000032 < / td > < / tr > < tr > < td > init < / td > < td > 0.000026 < / td > < / tr > < tr > < td > System lock < / td > < td > 0.000013 < / td > < / tr > < tr > < td > optimizing < / td > < td > 0.000013 < / td > < / tr > < tr > < td > statistics < / td > < td > 0.000198 < / td > < / tr > < tr > < td > preparing < / td > < td > 0.000019 < / td > < / tr > < tr > < td > executing < / td > < td > 0.000006 < / td > < / tr > < tr > < td > Sending data < / td > < td > 1.710508 < / td > < / tr > < tr > < td > end < / td > < td > 0.000024 < / td > < / tr > < tr > < td > query end < / td > < td > 0.000015 < / td > < / tr > < tr > < td > closing tables < / td > < td > 0.000019 < / td > < / tr > < tr > < td > freeing items < / td > < td > 0.003275 < / td > < / tr > < tr > < td > logging slow query < / td > < td > 0.000068 < / td > < / tr > < tr > < td > cleaning up < / td > < td > 0.000025 < / td > < / tr > < / tbody > < / table > < table > < thead > < tr > < th > id2=11111 < / th > < / tr > < / thead > < tbody > < tr > < td > Status < / td > < td > Duration < / td > < / tr > < tr > < td > starting < / td > < td > 0.000066 < / td > < / tr > < tr > < td > checking permissions < / td > < td > 0.000012 < / td > < / tr > < tr > < td > Opening tables < / td > < td > 0.000020 < / td > < / tr > < tr > < td > init < / td > < td > 0.000024 < / td > < / tr > < tr > < td > System lock < / td > < td > 0.000011 < / td > < / tr > < tr > < td > optimizing < / td > < td > 0.000013 < / td > < / tr > < tr > < td > statistics < / td > < td > 0.000021 < / td > < / tr > < tr > < td > preparing < / td > < td > 0.000015 < / td > < / tr > < tr > < td > executing < / td > < td > 0.000006 < / td > < / tr > < tr > < td > Sending data < / td > < td > 2.566770 < / td > < / tr > < tr > < td > end < / td > < td > 0.000025 < / td > < / tr > < tr > < td > query end < / td > < td > 0.000077 < / td > < / tr > < tr > < td > closing tables < / td > < td > 0.000034 < / td > < / tr > < tr > < td > freeing items < / td > < td > 0.004227 < / td > < / tr > < tr > < td > logging slow query < / td > < td > 0.000093 < / td > < / tr > < tr > < td > cleaning up < / td > < td > 0.000018 < / td > < / tr > < / tbody > < / table >

8 million data half 111 and half 222. The index you built is not differentiated. The mysql optimizer will choose full table scan instead of using the index. You can explain to check the search plan

query for long values of some fields
. 
show profile for query 13;
show profile for query 14;

it is recommended that you take a look at what causes the index to be long.
this is not comparable. The distribution of time1 and time2 values is not the same, resulting in longer indexed queries, probably because time1=1111111 has more records and therefore takes longer to return.

Previous: Python calls antiword to convert doc file and returns garbled code.

Next: Problem with front-end websocket shutting down ws.close ()

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1eb4a72-24f4.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1eb4a72-24f4.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?