Mysql query performance problem, the speed of adding order by is almost 50 times slower.

basic information:

The

datasheet has almost 10 million pieces of data, using the mycat sublibrary.

the indexes in the data table are
  1. PRIMARY id
  2. AppName (AppName, custidStatus, channel)
the table creation statement is as follows 
CREATE TABLE `eis_email_history` (
  `id` bigint(20) NOT NULL DEFAULT "0",
  `AppName` int(11) NOT NULL DEFAULT "0",
  `emailto` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT "" COMMENT "email",
  `emailfrom` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT "" COMMENT "email",
  `subject` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT "" COMMENT "",
  `content` text COLLATE utf8_unicode_ci NOT NULL COMMENT "",
  `sendtime` int(11) NOT NULL DEFAULT "0" COMMENT "unixtime",
  `sendstatus` tinyint(4) NOT NULL DEFAULT "0" COMMENT "0:1:2:3:",
  `channel` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT "" COMMENT "",
  `AmazonOrderId` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT "" COMMENT "???",
  `ASIN` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT "" COMMENT "asin",
  `attachment` text COLLATE utf8_unicode_ci COMMENT "2"tab" +"/" +"tab" ",
  `templateId` bigint(20) NOT NULL DEFAULT "0" COMMENT "Id",
  `custidStatus` int(20) NOT NULL DEFAULT "0" COMMENT "Id",
  PRIMARY KEY (`id`),
  KEY `emailto` (`emailto`(255)),
  KEY `sendtime` (`sendtime`),
  KEY `sendstatus` (`sendstatus`),
  KEY `emailfrom` (`emailfrom`(255)),
  KEY `asin` (`ASIN`),
  KEY `orderid` (`AmazonOrderId`),
  KEY `AppName` (`AppName`,`custidStatus`,`channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

first of all, there is no order by 

SELECT 
  `id`,
  `emailto`,
  `channel`,
  `AppName`,
  `AmazonOrderId` 
FROM
  `eis_email_history` 
WHERE `AppName` = 21 
  AND `custidStatus` IN (0, 1, 2) 
  AND `channel` = "***" 
LIMIT 50
this query is basically a second query, and then the result of explain is

order by id desc,

1explain

.
depending on the situation, the index of the where condition is not used due to the addition of order by, but the primary key scan is used
Mysql mysql-optimized msyql-order
Apr.13,2021
The main reason for the slowness of

is sorting, especially the sorting of fragments.
mycat will sort limit50, in all shards and then sort limit50 again in mycat memory
if there is no sorting, mycat only needs to take 50 entries of a shard at random. The amount of calculation varies greatly. The more shards, the slower the
. If you really need to improve your writing
, if you sort by primary key, the index of innodb has a primary key.
so where plus order can go to the index (overwrite the index), as long as the select cannot have columns other than the index field 

SELECT 
  a.`id`,
  a.`emailto`,
  a.`channel`,
  a.`AppName`,
  a.`AmazonOrderId` 
from eis_email_history a join 
(select id FROM
  `eis_email_history` 
 WHERE `AppName` = 21 
  AND `custidStatus` IN (0, 1, 2) 
  AND `channel` = '***' 
 ORDER BY id DESC 
 LIMIT 50) b 
on a.id=b.id;
For

query optimization of order by , please take a look at

mainly introduces two methods:

  • the first is FORCE INDEX (PRIMARY) : this straightforward understanding is to force indexing
    • . The second
  • is late row lookups , which is the focus of the article, that is, to construct a subquery with only id, and then join together. This greatly improves efficiency. The following sample code, o is through your table and only id query out of the temporary word table, l is to join together contains all the fields of the table. 
SELECT  xx,xxx,....
FROM    (
        SELECT  id
        FROM    <>
        ORDER BY
                id
        LIMIT <>
        ) o 
JOIN    <> l
ON      l.id = o.id  
ORDER BY
        l.id

put the range query last;

Index:
AppName (AppName, channel, custidStatus) 

  https://codeshelper.com/a/11.

Previous: What does typescript generic class mean?

Next: How does andlabs/ui get the value of ui.NewDateTimePicker ()

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-1e4c890-44ecf.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-1e4c890-44ecf.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?