phpmyadmin Question posted in
The official documentation can be found here.

Slow SQL query (multiple selects and LIMIT 1 in each one) – Phpmyadmin

mysql-nodejs is slower than executing an sql query directly in phpMyAdmin, However, I need to optimize this query because it’s now taking 10sec.
I think that it’s performing searches for every row in g which is taking too much time.

This is the query I tried

SELECT *, 
       (SELECT Concat(col1, ' AT ', coldate) 
        FROM   tbl1 
        WHERE  txt = g.txt 
               AND keyword = g.keyword 
               AND user = g.user 
               AND coldate < g.coldate 
        ORDER  BY coldate DESC 
        LIMIT  1) AS LastOneBefore, 
       (SELECT Concat(col1, ' AT ', coldate) 
        FROM   tbl1 
        WHERE  txt = g.txt 
               AND keyword = g.keyword 
               AND user = g.user 
               AND coldate < ( Now() - INTERVAL 7 day ) 
        ORDER  BY coldate DESC 
        LIMIT  1) AS LastOneBefore7days, 
       (SELECT Concat(col1, ' AT ', coldate) 
        FROM   tbl1 
        WHERE  txt = g.txt 
               AND keyword = g.keyword 
               AND user = g.user 
               AND coldate < ( Now() - INTERVAL 30 day ) 
        ORDER  BY coldate 
        LIMIT  1) AS LastOneBefore30days 
FROM   (SELECT * 
        FROM   (SELECT * 
                FROM   tbl1 
                WHERE  txt = "txt1" 
                       AND user = "user1" 
                ORDER  BY coldate DESC) k 
        GROUP  BY txt, 
                  keyword) g

can it be optimized to take less execution time?

tbl1 Columns:
id(int) , user (varchar) , coldate (Date) , txt (varchar) , keyword (varchar) , col1 (int)

Example of Data:

id      user    coldate     txt     keyword     col1
1       usr1    23/07/2019  txt1    kwd1        1000
2       usr1    20/07/2019  txt1    kwd1        1100
3       usr1    12/07/2019  txt1    kwd1        900
4       usr1    23/07/2019  txt1    kwd2        10100
5       usr1    23/07/2019  txt2    kwd1        1000
6       usr1    23/07/2019  txt2    kwd2        1000
7       usr1    20/06/2019  txt2    kwd2        1000

so I need to output a row for each group with same kwd and txt like this

id      user    coldate     txt     keyword     col1   LastOneBefore                LastOneBefore7days          LastOneBefore30days
1       usr1    23/07/2019  txt1    kwd1        1000   1100 at 20/07/2019           900 at 12/07/20194                  null
4       usr1    23/07/2019  txt1    kwd2        10100   null                            null                            null
6       usr1    23/07/2019  txt2    kwd2        1000    1000 at 20/06/2019            null                          1000 at 20/06/2019
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I managed to get these columns (LastOneBefore7days, LastOneBefore30days) this way but still can't get LastOneBefore

    SELECT *, 'Latest' as Type FROM (  SELECT * FROM tbl1 WHERE  txt="txt1"  AND User="usr1"    Order by coldate DESC ) k GROUP BY txt,keyword
UNION ALL

SELECT *, 'Before30Days' as Type FROM (  SELECT * FROM tbl1 WHERE      txt="txt1"  AND User="usr1"  AND coldate < (NOW() - INTERVAL 30 DAY)   Order by coldate DESC ) k GROUP BY txt,keyword

UNION ALL

SELECT *, 'Before7Days' as Type FROM (  SELECT * FROM tbl1 WHERE  txt="txt1"  AND User="usr1"  AND coldate < (NOW() - INTERVAL 7 DAY)   Order by coldate DESC ) k GROUP BY txt,keyword

  2. 0

    Please try to RUN this query in your code

    I hope it’ll give you output as expected and give update to me so I can give expected solution to you 

    SELECT *, 
CASE
WHEN  coldate < g.coldate  THEN  Concat(col1, ' AT ', coldate)
ELSE 0 END AS LastOneBefore,
CASE
WHEN coldate < ( Now() - INTERVAL 7 day )  THEN Concat(col1, ' AT ', coldate)
ELSE 0 END AS LastOneBefore7days,
CASE
WHEN coldate < ( Now() - INTERVAL 30 day ) THEN Concat(col1, ' AT ', coldate)
ELSE 0 END AS LastOneBefore30days

FROM   
(
        SELECT * 
        FROM   (SELECT * 
                FROM   tbl1 
                WHERE  txt = "txt1" 
                       AND user = "user1" 
                ORDER  BY coldate DESC) k 
        GROUP  BY txt, 
                  keyword
) g 
WHERE   txt = g.txt           
AND keyword = g.keyword 
AND user = g.user
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search