Alternative of NOT EXISTS in MySQL as query is taking time to execute with NOT EXISTS

Question

Dummy table:

id   FileName    DateLastSaved
1    Marium.doc    2015-01-01
2    Amna.doc      2016-01-01
3    Marium.doc    2016-01-01

I want the query to return such rows where FileName is unique in the whole table. Rows should be returned for particular date range. Suppose date ranges are of 2016 only, so third row should not be returned as FileName is not unique.

The query that I have created is:

$presentquery="SELECT * FROM InitialLog i WHERE MDid='$MDid' AND 
(DateLastSaved>='$firstdate' AND DateLastSaved<='$presentdate') AND NOT
 EXISTS (SELECT id FROM InitialLog i2  WHERE i2.id<i.id AND i.FileName=i2.FileName )";

(Where $firstdate and $presentdate are 2 dates for date ranges)

The query is returning the accurate results but it's taking time to execute. Is there any other way that I can rewrite this query??

(I have table with many rows)

Well,there is `NOT IN(subselect)` and `LEFT JOIN x WHERE x.a =NULL;` And they will all benefit from relevant indexes. — wildplasser, Mar 29 '17 at 22:13
Try giving this a read: https://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work — Shadow, Mar 29 '17 at 22:15
Your main issue is not so much the `NOT EXISTS` as it is the fact it is done on a **correlated** subquery. — Uueerdo, Mar 29 '17 at 22:35

wogsland · Answer 1 · 2017-03-29T22:54:59.443

0

You can get the same logic using a LEFT JOIN and looking for nulls, that is,

$presentquery = "SELECT DISTINCT i.* FROM InitialLog i
LEFT JOIN InitialLog i2 ON i2.id<i.id AND i.FileName=i2.FileName
WHERE i.MDid='$MDid'
AND i.DateLastSaved>='$firstdate'
AND i.DateLastSaved<='$presentdate'
AND i2.id IS NULL";

This way you are doing a single join rather than subquerying against each value in i.

edited Mar 29 '17 at 22:54

answered Mar 29 '17 at 22:23

wogsland

9,106
19
57
93

Not returning the desired result. Used your query and then my query, results aren't same. – Sarah Mandana Mar 29 '17 at 22:34
@MariumMalik Because you wanted distinct rows from `i` I'm guessing? I altered the query accordingly. – wogsland Mar 29 '17 at 22:38
I don't see how your suggestion's results would be any less distinct than his original query. @MariumMalik, how did the results differ? (If they were just in a different order that is to be expected; MySQL doesn't even guarantee same order on identical queries if they omit an `ORDER BY` clause). – Uueerdo Mar 29 '17 at 22:39
I would suggest using `i.id != i2.id` instead of `i2.id – Don't Panic Mar 29 '17 at 22:40
@Don'tPanic it looks like the original query is trying to find the earliest occurrence of each filename; changing `<` to `!=` would be a different query altogether. – Uueerdo Mar 29 '17 at 22:45
@Uueerdo I am not talking about the order. They are completely different. – Sarah Mandana Mar 29 '17 at 22:48
@Uueerdo there can be no earliest occurrence of a unique filename. – Don't Panic Mar 29 '17 at 22:48
@Don'tPanic Ah, I overlooked that... wouldn't that ensure the entire `NOT EXISTS (...)` portion of the query was pointless? Edit: Oh, I see (sort of), they want only filenames that are unique. ...still not sure how results could differ though. Oh!.. – Uueerdo Mar 29 '17 at 22:50
@wogsland You may need to add `i.` to all the unqualified column references in the `WHERE` clause; I'm surprised it even ran without them. – Uueerdo Mar 29 '17 at 22:54

score 0 · Answer 2 · answered Mar 29 '17 at 22:24

0

I put this query together and it returns the results very quickly.

Select *
FROM foo
Where (`datelastsaved` > '2015-12-31' && `datelastsaved` < '2017-01-01')
    AND `filename` NOT IN (
         Select `filename`
         FROM foo
         GROUP BY `filename`
         HAVING COUNT(*) > 1);

The first part is your normal select statement with the where clauses to filter on the dates.

The second part is the NOT IN where the select statement finds all of the ones with duplicate filenames.

Select `filename`  FROM foo GROUP BY `filename` HAVING COUNT(*) > 1)

answered Mar 29 '17 at 22:24

Johnish

81
6

Your query is taking more time than my own query (I have table with many rows) – Sarah Mandana Mar 29 '17 at 22:43
Do you have an index on filename? – Johnish Mar 31 '17 at 15:28

Uueerdo · Answer 3 · 2017-03-29T23:26:48.310

0

It looks like you are trying to get the data associated with the first occurrence of each file name, this should work:

SELECT * 
FROM InitialLog i 
WHERE MDid='$MDid' 
   AND DateLastSaved>='$firstdate' 
   AND DateLastSaved<='$presentdate'
   AND id IN (SELECT MIN(id) FROM InitialLog GROUP BY FileName)
;

Alternatively, you can do a JOIN with the same subquery instead:

SELECT i.* 
FROM InitialLog AS i 
   INNER JOIN (SELECT MIN(id) AS id 
               FROM InitialLog 
               GROUP BY FileName
   ) AS firsts USING (id)
WHERE i.MDid='$MDid' 
   AND i.DateLastSaved>='$firstdate' 
   AND i.DateLastSaved<='$presentdate'
;

edited Mar 29 '17 at 23:26

answered Mar 29 '17 at 22:48

Uueerdo

15,723
1
16
21

I'm interested to learn if this or the join turns out to be faster. – wogsland Mar 29 '17 at 22:55
Yeah, it is hard to say, and which is faster is probably **very** data dependent. – Uueerdo Mar 29 '17 at 22:57
Thanks for the help! But my query is till faster than this one. Both are taking much more time to execute. – Sarah Mandana Mar 31 '17 at 11:09
Are you checking without result caching? – Uueerdo Mar 31 '17 at 16:47
Yes, without it – Sarah Mandana Mar 31 '17 at 19:57
Just as a test, what if you take of the `'`'s around `$MDid` (assuming it is not actually a string; you don't actually show it in your sample data)? – Uueerdo Mar 31 '17 at 21:49

Alternative of NOT EXISTS in MySQL as query is taking time to execute with NOT EXISTS

3 Answers3