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