MySQL multiple Sending Data states

Question

My query runs too long. When I profile it I see something like that:

    Sending data    0.039324
    executing   0.000011
    Sending data    0.039662
    executing   0.000012
    Sending data    0.040380
    executing   0.000015
    Sending data    0.035879
    executing   0.000012
    Sending data    0.035426
    executing   0.000012
    Sending data    0.038107
    executing   0.000011
    Sending data    0.035247
    executing   0.000011
    Sending data    0.050108
    executing   0.000014
    Sending data    0.045458
    executing   0.000012
    Sending data    0.034700
    executing   0.000012
    Sending data    0.036205
    executing   0.000012
    Sending data    0.034602
    executing   0.000015
    Sending data    0.034580
    executing   0.000012
    Sending data    0.034477
    executing   0.000010
    Sending data    0.034382
    executing   0.000010
    Sending data    0.034416
    executing   0.000011
    Sending data    0.034335
    executing   0.000010
    Sending data    0.034474
    executing   0.000010
    Sending data    0.034405
    executing   0.000010
    Sending data    0.034433
    executing   0.000011
    Sending data    0.034544
    executing   0.000010
    Sending data    0.034525
    executing   0.000011
    Sending data    0.034459
    executing   0.000010
    Sending data    0.034766
    executing   0.000011
    Sending data    0.034633
    executing   0.000010
    Sending data    0.034574
    executing   0.000011
    Sending data    0.034607
    executing   0.000010
    Sending data    0.034613
    executing   0.000011
    Sending data    0.034394
    executing   0.000010
    Sending data    0.034606
    executing   0.000011
    Sending data    0.034790
    executing   0.000011
    Sending data    0.034614
    executing   0.000011
    Sending data    0.034497
    executing   0.000010
    Sending data    0.034756
    executing   0.000010
    Sending data    0.034440
    executing   0.000010
    Sending data    0.034414
    executing   0.000011
    Sending data    0.034484
    executing   0.000011
    Sending data    0.034490
    executing   0.000011
    Sending data    0.034672
    executing   0.000011
    Sending data    0.034455
    executing   0.000011
    Sending data    0.034430
    executing   0.000011
    Sending data    0.034509
    executing   0.000012
    Sending data    0.034432
    executing   0.000012
    Sending data    0.034348
    executing   0.000011
    Sending data    0.034378
    executing   0.000011
    Sending data    0.034356
    executing   0.000011
    Sending data    0.034631
    end 0.000014
    query end   0.000007
    closing tables  0.000010
    freeing items   0.000025
    logging slow query  0.000003
    logging slow query  0.000004
    cleaning up 0.000004

There's too much sending data in it.

Query I ran:

SELECT COUNT(*) as count from OrdersArchive where ID>0 and PId IN ('2564') and 
   (
   ID like '17000106864' 
   OR `OrderID` like '17000106864' 
   OR `ID` IN
   (
       SELECT `transferID`
       FROM `custom_fields`
       WHERE `fieldName` = 'invoiceNumber'
       AND `value` like '%17000106864%'
   )
   OR `tpb` LIKE '17000106864' 

   )

Explain shows

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY OrdersArchive   range   PRIMARY,ID_UNIQUE   PRIMARY 4   NULL    41609   Using where
2   DEPENDENT SUBQUERY  custom_fields   ALL NULL    NULL    NULL    NULL    93141   Using where

MySQL table structures:

CREATE TABLE IF NOT EXISTS `OrdersArchive` (
  `ID` int(11) NOT NULL,
  `ids` int(11) NOT NULL DEFAULT '0',
  `OrderID` varchar(11) NOT NULL DEFAULT '0',
  `PricePosition` int(11) NOT NULL DEFAULT '0',
  `Reverse` tinyint(1) DEFAULT NULL,
  `DataOrder` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DataFlightTrain` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Customer` varchar(255) DEFAULT NULL,
  `PhoneCustomer` varchar(255) DEFAULT NULL,
  `EmailCustomer` varchar(255) DEFAULT NULL,
  `Provider` int(11) DEFAULT NULL,
  `DeliveryTime` timestamp NULL DEFAULT NULL,
  `Address1` varchar(255) DEFAULT NULL,
  `Address2` varchar(255) NOT NULL,
  `Passangers` varchar(1024) DEFAULT NULL,
  `PassangersPhones` varchar(255) NOT NULL,
  `PassangersEmailes` varchar(255) NOT NULL,
  `FlightTrain` varchar(255) DEFAULT NULL,
  `QuantityPassangers` int(11) DEFAULT '1',
  `NamePlate` varchar(255) DEFAULT NULL,
  `PhoneDriver` varchar(255) DEFAULT NULL,
  `PhoneDriverNeed` tinyint(1) DEFAULT '0',
  `Status` int(11) DEFAULT NULL,
  `Operator` int(11) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `usn` varchar(256) NOT NULL,
  `ArendaNeed` varchar(255) DEFAULT '',
  `ArendaHour` int(11) DEFAULT NULL,
  `ArendaMinutes` varchar(255) DEFAULT '',
  `Cost` double DEFAULT NULL,
  `Notes` text NOT NULL,
  `notes2` varchar(256) NOT NULL DEFAULT '',
  `PId` int(11) NOT NULL DEFAULT '0',
  `Voucher` varchar(256) NOT NULL,
  `Invoice` varchar(256) NOT NULL,
  `Meet` varchar(255) NOT NULL,
  `Toward` varchar(255) NOT NULL,
  `techStatus` int(2) NOT NULL DEFAULT '0',
  `City` varchar(55) NOT NULL,
  `City2` varchar(55) NOT NULL,
  `Auto` varchar(30) NOT NULL,
  `department` varchar(255) NOT NULL DEFAULT '',
  `nsktime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tpb` varchar(255) NOT NULL DEFAULT '',
  `ban_add_races` int(1) NOT NULL DEFAULT '0',
  `paid` int(10) NOT NULL DEFAULT '0',
  `taxi` varchar(255) NOT NULL DEFAULT '',
  `price_client` int(11) DEFAULT NULL,
  `comission_from_client` int(11) DEFAULT NULL,
  `primechanie` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `fk_Orders_Users1_idx` (`Operator`),
  KEY `fk_Orders_Providers1_idx` (`Provider`),
  KEY `fk_Orders_OrderStatus1_idx` (`Status`),
  KEY `ids` (`ids`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## and other table

CREATE TABLE IF NOT EXISTS `custom_fields` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `transferID` int(11) NOT NULL,
  `fieldName` varchar(255) NOT NULL,
  `value` varchar(1024) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=325452 ;

You're missing some indexes, like OrderID and tpb in the OrdersArchive tablet. The custom_fields table will always be quite inefficient. Anyway, I assume you want your query to run faster, how much faster? Don't forget that the platform you're running the query on will affect speed as well. — KIKO Software
– KIKO Software, Commented Aug 30, 2017 at 8:12
Added indexes you mentioned, stiil bad results like querying 2 minutes. Deleting subquery from query really speed-up the perfomance to nearly 0.1 sec — Влад Чачиев
– Влад Чачиев, Commented Aug 30, 2017 at 8:19
The subquery returns a single result, so it doesn't need to be a subquery. I don't expect MySQL will run it for every row of OrdersArchive, but it might. Since you are using PHP, try to do the subquery first, and then generate the OR ID IN (.....) with PHP so it will be a constant array of ID's. How many invoices could there be? — KIKO Software
– KIKO Software, Commented Aug 30, 2017 at 8:30
AND value like '%17000106864%' This is slow because '%...' cannot use an index — Strawberry
– Strawberry, Commented Aug 30, 2017 at 8:31
I'm trying to rewrite query to use JOIN, but now it send count = 2, but real count is 1. Query: SELECT DISTINCT COUNT(*) as count FROM OrdersArchive INNER JOIN custom_fields on OrdersArchive.ID = custom_fields.transferID WHERE OrdersArchive.ID>0 AND OrdersArchive.PId IN ('2564') AND ( OrdersArchive.ID LIKE '17000106864' OR OrdersArchive.OrderID LIKE '17000106864' OR OrdersArchive.tpb LIKE '17000106864' OR ( custom_fields.fieldName = 'invoiceNumber' AND custom_fields.value like '%17000106864%' ) ) — Влад Чачиев
– Влад Чачиев, Commented Aug 30, 2017 at 8:43

SpacemanSpiff · Accepted Answer · 2017-08-30 08:54:14Z

0

While the current SELECT query could probably be improved to some degree I think it would help much more if you could store the data in a more efficient way, especially if you can remove the need for

AND value like '%17000106864%'

If you create a separate field for invoiceNumber and populate it when inserting your data you could have it indexed and select/join it like this:

WHERE invoiceNumber=17000106864

In case you are only looking for one record adding a LIMIT to the query would also help.

answered Aug 30, 2017 at 8:54

SpacemanSpiff

2498 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Влад Чачиев · Accepted Answer · 2017-08-30 09:33:21Z

0

Well, I accomplished this with INNER JOIN. Runs 0.34 sec

Final query is:

SELECT COUNT(`OrdersArchive`.ID) as count, `OrdersArchive`.ID
FROM `OrdersArchive`
INNER JOIN `custom_fields` on `OrdersArchive`.ID = `custom_fields`.`transferID`
WHERE `OrdersArchive`.ID>0 
AND `custom_fields`.`fieldName` = 'invoiceNumber'
AND 
`OrdersArchive`.PId IN ('2564') AND
(
   `OrdersArchive`.ID LIKE '17000106864' 
   OR `OrdersArchive`.`OrderID` LIKE '17000106864' 
   OR `OrdersArchive`.`tpb` LIKE '17000106864' 
   OR (
 `custom_fields`.`value` like '%17000106864%'
   )
)

answered Aug 30, 2017 at 9:33

Влад Чачиев

331 silver badge5 bronze badges

Comments

Rick James · Accepted Answer · 2017-09-02 20:45:00Z

Back to the question implied in the title ("multiple Sending Data states")...

IN(SELECT ...) is often very inefficient. In your case it is being repeatedly executed, thereby "executing" and "sending data" for each invocation.

The other Answers provide address the other implied question ("query runs too long") by turning that construct into a JOIN.

Other issues;

PRIMARY KEY (`ID`),   -- This is UNIQUE and an INDEX
UNIQUE KEY `ID_UNIQUE` (`ID`),  -- totally redundant; DROP it

You are probably being plagued by performance issues since the schema is "EAV" (Entity-Attribute-Value) and you need to filter based on some key-value ("invoiceNumber"). Consider pulling it out of the key-value table (custom_fields) and put it into the main table (OrdersArchive).

But then I see that OrdersArchive has a rather large number of columns. But I don't have any concrete suggestion about that.

custom_fields is quite bereft of indexes. See my tips on indexing a common key-value table.

Collectives™ on Stack Overflow

MySQL multiple Sending Data states

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related