MySQL Forums
Forum List  »  InnoDB

Re: Help on Query with DATE BETWEEN not performant
Posted by: Kristijan Marin
Date: February 09, 2020 08:46AM

Peter Brawley Wrote:
-------------------------------------------------------
>
> If I understand you correctly, you've benchmarked
> the inner query and found its performance
> acceptable.
>
> Re the outer query, function calls and LIKE
> '%...%' in the Where clause prevent index use for
> the clause, so let's see the results of ...
>
> Explain
>
> Explain Format=Tree
>
> Explain Analyze
>
> ... (i) on the entire query, and (ii) on the query
> without its outer Where clause.

I tried to do Format=tree and Analyze but apparently this is not supported yet in our 5.7.19 cause I'm getting unknown explain format name and something about Mysql error near Analyse...

I did run Explain extended on thew whole query:

+----+-------------+---------------------+------------+--------+-------------------------------------+----------+---------+--------------------------------------+------+----------+---------------------------------+
| id | select_type | table               | partitions | type   | possible_keys                       | key      | key_len | ref                                  | rows | filtered | Extra                           |
+----+-------------+---------------------+------------+--------+-------------------------------------+----------+---------+--------------------------------------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2>          | NULL       | ALL    | NULL                                | NULL     | NULL    | NULL                                 |  864 |   100.00 | Using temporary; Using filesort |
|  1 | PRIMARY     | E                   | NULL       | eq_ref | PRIMARY                             | PRIMARY  | 4       | A.fk_earticle                        |    1 |   100.00 | Using where                     |
|  2 | DERIVED     | ecust_user_contract | NULL       | ref    | PRIMARY,CUC_contract,CUC_user       | CUC_user | 152     | const                                |   54 |   100.00 | Using index; Using temporary    |
|  2 | DERIVED     | CA                  | NULL       | ref    | PRIMARY,contractCDate,contractADate | PRIMARY  | 152     | test.ecust_user_contract.fk_contract |  144 |    11.11 | Using where                     |
+----+-------------+---------------------+------------+--------+-------------------------------------+----------+---------+--------------------------------------+------+----------+---------------------------------+
4 rows in set, 2 warnings (0.00 sec)


This is on the subquery only:

SELECT count(*) from econtracts_articles AS CA 
WHERE CA.fk_econtract IN (SELECT fk_contract FROM ecust_user_contract WHERE fk_cust_user='ukommiha') AND CA.articledate BETWEEN '2018.01.01' AND '2020.02.02';


+----+-------------+---------------------+------------+------+-------------------------------------+----------+---------+--------------------------------------+------+----------+------------------------------+
| id | select_type | table               | partitions | type | possible_keys                       | key      | key_len | ref                                  | rows | filtered | Extra                        |
+----+-------------+---------------------+------------+------+-------------------------------------+----------+---------+--------------------------------------+------+----------+------------------------------+
|  1 | SIMPLE      | ecust_user_contract | NULL       | ref  | PRIMARY,CUC_contract,CUC_user       | CUC_user | 152     | const                                |   54 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | CA                  | NULL       | ref  | PRIMARY,contractCDate,contractADate | PRIMARY  | 152     | test.ecust_user_contract.fk_contract |  144 |    11.11 | Using where                  |
+----+-------------+---------------------+------------+------+-------------------------------------+----------+---------+--------------------------------------+------+----------+------------------------------+
2 rows in set, 2 warnings (0.00 sec)


If I force index contractADate I get this:
+----+-------------+---------------------+------------+------+-------------------------------+---------------+---------+--------------------------------------+------+----------+------------------------------+
| id | select_type | table               | partitions | type | possible_keys                 | key           | key_len | ref                                  | rows | filtered | Extra                        |
+----+-------------+---------------------+------------+------+-------------------------------+---------------+---------+--------------------------------------+------+----------+------------------------------+
|  1 | SIMPLE      | ecust_user_contract | NULL       | ref  | PRIMARY,CUC_contract,CUC_user | CUC_user      | 152     | const                                |   54 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | CA                  | NULL       | ref  | contractADate                 | contractADate | 152     | test.ecust_user_contract.fk_contract |  371 |    11.11 | Using index condition        |
+----+-------------+---------------------+------------+------+-------------------------------+---------------+---------+--------------------------------------+------+----------+------------------------------+
2 rows in set, 2 warnings (0.00 sec)


For instance the total number of records (in subquery) without using date period is 669.645 hits.
Using the periode I get 380.060 hits.

No matter what periode I put into the subquery, the explain plan shows the same numbers (144 in above example) ... unless I set the query to the same day ... and this is the only time my date index is used:
+----+-------------+---------------------+------------+------+-------------------------------------+---------------+---------+--------------------------------------------+------+----------+--------------------------+
| id | select_type | table               | partitions | type | possible_keys                       | key           | key_len | ref                                        | rows | filtered | Extra                    |
+----+-------------+---------------------+------------+------+-------------------------------------+---------------+---------+--------------------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | ecust_user_contract | NULL       | ref  | PRIMARY,CUC_contract,CUC_user       | CUC_user      | 152     | const                                      |   54 |   100.00 | Using index              |
|  1 | SIMPLE      | CA                  | NULL       | ref  | PRIMARY,contractCDate,contractADate | contractADate | 155     | test.ecust_user_contract.fk_contract,const |    7 |   100.00 | Using where; Using index |
+----+-------------+---------------------+------------+------+-------------------------------------+---------------+---------+--------------------------------------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.01 sec)


So according to plans, I would say that the engine thinks it is quicker to use primary?
but the primary is actually getting 380.060 hits which are then filtered by date, or am I wrong?


This is a create of econtracts_articles table:
CREATE TABLE `econtracts_articles` (
  `fk_econtract` varchar(50) NOT NULL,
  `fk_earticle` int(11) NOT NULL,
  `grade` int(11) NOT NULL DEFAULT '0',
  `comment` varchar(500) DEFAULT NULL,
  `translation` varchar(50) DEFAULT NULL,
  `trans_title` varchar(100) DEFAULT NULL,
  `trans_subtitle` varchar(255) DEFAULT NULL,
  `trans_author` varchar(50) DEFAULT NULL,
  `trans_fulltext` text,
  `alarmclip` int(1) unsigned NOT NULL DEFAULT '0',
  `articledate` date NOT NULL,
  `createdatet` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`fk_econtract`,`fk_earticle`),
  KEY `CA_article` (`fk_earticle`),
  KEY `CA_artcon` (`fk_earticle`,`fk_econtract`),
  KEY `contractCDate` (`fk_econtract`,`createdatet`) USING BTREE,
  KEY `contractADate` (`fk_econtract`,`articledate`) USING BTREE,
  CONSTRAINT `CA_article` FOREIGN KEY (`fk_earticle`) REFERENCES `earticles` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `CA_contract` FOREIGN KEY (`fk_econtract`) REFERENCES `econtracts` (`code`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


>
> And re the Where clause ...
>
> (i) Upper() is redundant, and with all those
> spaces passed, just Concat() will do,
>
> (ii) if you often have to scope on a concatenation
> of five(!) string columns, the database design
> needs work. Consider a derived column that
> precomputes that concatenation, and/or
> normalisation of that wedge of information to a
> key.
>

The five fields are like this cause before, we used full-text index on them, but now that we tried to do something, we saw that the Fulltext index
was a wrong decision so we dropped the index and re-wrote our queries with LIKE to get better results ... which we definitely do ...

>
> 2 Re conversion to utf6mb4
>
> 1.5 days to Alter a few million rows is absurd.
> Something in your system is wackydoodle.
>
> Two MySQL tricks to speed up Alter Table ... (i)
> drop all indexes first, then Alter Table, then
> recreate needed indeses, (ii) this can be faster
> than Alter Table..
>
> Create Table newtbl
> Select
> ...,
> CONVERT( BINARY(CONVERT(col USING UTF8) )
> 8) ) USING utf8mb4),
> ...;
> ... then rename new and old tables. Test first,
> , charsets are tricky.


Great hint, will do that ...

Thanks,
Kris

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help on Query with DATE BETWEEN not performant
40
February 09, 2020 08:46AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.