Help on Query with DATE BETWEEN not performant

[Kristijan Marin]

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