Hi guys,
I thought I had a simple query, but it looks like I can't make it run faster.
My Scenario:
1)I need to find all the 'contracts' for a customer
2)then get all the 'articles' for all 'contracts'
3) and then display only those that are inside my date period.
4) so I end up with all the articles that some customer could read
Stats:
- earticles has 3.4mil rows
- econtracts_articles has 7.8mil rows
Running query as it is can take up to 5min !! Running the inner query (description is in the text below) is returning rows in 0.3sec ?!?
I have a query with joins like :
SELECT DISTINCT A.ID from earticles as A
JOIN econtracts_articles AS CA ON A.ID = CA.fk_earticle AND CA.fk_econtract IN (SELECT fk_contract FROM ecust_user_contract WHERE fk_cust_user='mihaluka')
WHERE A.dateOfArticle BETWEEN '2019.01.02' AND '2020.02.02'
and my explain plan is:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ecust_user_contract
partitions: NULL
type: ref
possible_keys: PRIMARY,CUC_contract,CUC_user
key: CUC_user
key_len: 152
ref: const
rows: 3
filtered: 100.00
Extra: Using index; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: CA
partitions: NULL
type: ref
possible_keys: PRIMARY,CA_article,CA_artcon
key: PRIMARY
key_len: 152
ref: test.ecust_user_contract.fk_contract
rows: 263
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: A
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,article_media,seed,dateOfArticle,createDate,art_genre_idx,createDateId
key: PRIMARY
key_len: 4
ref: test.CA.fk_earticle
rows: 1
filtered: 38.88
Extra: Using where
3 rows in set, 1 warning (0.00 sec)
So somehow I can't make my query to :
1) get the list of all articles for my contracts (I can do that)
2) but then filter the list got in step 1, against the date criteria
The mentioned query is working but is very SLOW ...
I then tried to somehow isolate the query to get exactly the articles I need to check against the date criteria by rewriting the query to :
SELECT A.ID FROM
(SELECT E.ID,E.dateOfArticle from econtracts_articles AS CA
JOIN earticles AS E ON CA.fk_earticle = E.ID
WHERE CA.fk_econtract IN (SELECT fk_contract FROM ecust_user_contract WHERE fk_cust_user= 'mihaluka' )) as A
WHERE A.dateOfArticle BETWEEN '2019.01.01' AND '2020.01.01'
But the result was the same
But what I found out is .... that IF I execute ONLY the INNER query, so this one :
SELECT E.ID,E.dateOfArticle from econtracts_articles AS CA
JOIN earticles AS E ON CA.fk_earticle = E.ID
WHERE CA.fk_econtract IN (SELECT fk_contract FROM ecust_user_contract WHERE fk_cust_user= 'mihaluka' )
Than this is super quick ! Like in millis.
and explain is :
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ecust_user_contract
partitions: NULL
type: ref
possible_keys: PRIMARY,CUC_contract,CUC_user
key: CUC_user
key_len: 152
ref: const
rows: 3
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: CA
partitions: NULL
type: ref
possible_keys: PRIMARY,CA_article,CA_artcon
key: PRIMARY
key_len: 152
ref: test.ecust_user_contract.fk_contract
rows: 263
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: E
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.CA.fk_earticle
rows: 1
filtered: 100.00
Extra: NULL
3 rows in set, 1 warning (0.00 sec)
Here is my table for earticles:
CREATE TABLE `earticles` (
`id` int(11) NOT NULL,
`title` varchar(500) CHARACTER SET utf8 DEFAULT NULL,
`subtitle` varchar(600) CHARACTER SET utf8 DEFAULT NULL,
`body` longtext CHARACTER SET utf8,
`author` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`summary` text CHARACTER SET utf8,
`fk_media` varchar(50) CHARACTER SET utf8 NOT NULL,
`dateOfArticle` date NOT NULL,
`onpage` int(11) NOT NULL DEFAULT '0',
`starttime` datetime DEFAULT NULL,
`duration` datetime DEFAULT NULL,
`http` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`nrscanpages` int(11) NOT NULL DEFAULT '0',
`totalunits` decimal(18,2) NOT NULL DEFAULT '0.00',
`fk_news` int(11) NOT NULL DEFAULT '0',
`seed` varchar(50) CHARACTER SET utf8 NOT NULL,
`costperunit` decimal(18,2) NOT NULL DEFAULT '0.00',
`category` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`show` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`createdate` timestamp NULL DEFAULT NULL,
`fk_genre` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`fk_show` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `article_media` (`fk_media`),
KEY `seed` (`seed`),
KEY `dateOfArticle` (`dateOfArticle`),
KEY `createDate` (`createdate`),
KEY `art_genre_idx` (`fk_genre`),
KEY `createDateId` (`createdate`,`id`),
CONSTRAINT `art_genre` FOREIGN KEY (`fk_genre`) REFERENCES `egenre` (`code`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `art_media` FOREIGN KEY (`fk_media`) REFERENCES `emedias` (`code`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
Any hint on how to fix my problem are more then WELCOME!
Thank you,
Kris