MySQL Select less than and other duplicated records

[peterjc]

Hi

Here is example:of my question.

 

id-----date------ number

1 2012-05-01 100

2 2012-05-01 101

3 2012-05-01 102

4 2012-05-02 103

5 2012-05-03 104

6 2012-05-04 105

7 2012-05-04 101

8 2012-05-04 102

9 2012-05-05 106

 

First, I want to select all records <= 2012-05-03

So, the result will look like below:

 

id------date------ number

1 2012-05-01 100

2 2012-05-01 101

3 2012-05-01 102

4 2012-05-02 103

5 2012-05-03 104

 

But, now, i also want to include the records below. Because the number 101 and 102 also exist in the query above.

 

id------date------ number

7 2012-05-04 101

8 2012-05-04 102

 

 

So, The final result that i want is:

 

id------date------ number

1 2012-05-01 100

2 2012-05-01 101

3 2012-05-01 102

4 2012-05-02 103

5 2012-05-03 104

7 2012-05-04 101

8 2012-05-04 102

 

Could anyone please help how to write the sql query? There are about 100 thousand records in the database table.

 

Hope you guys understand what i means, thanks in advance.

[Psycho]

SELECT *
FROM table
WHERE number IN
   (SELECT number
 FROM table
 WHERE date <= '2012-05-03')

Edited January 28, 2013 by Psycho

[peterjc]

SELECT *
FROM table
WHERE number IN
(SELECT number
 FROM table
 WHERE date <= '2012-05-03')

 

Tried and it will freeze my PC! If without the subquery, it's fast, but if add in the sub-query, it will freeze my PC There are around 100K of records. Just for info only, there are indexing for the table

 

Anyway to solve this?

 

Thanks.

Edited January 28, 2013 by peterjc

[Barand]

Try this

 

SELECT t.id, t.date, t.number
FROM tablename as t
   WHERE date <= '2012-05-03'
UNION
SELECT t.id, t.date, t.number
FROM tablename as t
INNER JOIN
   (
   SELECT DISTINCT number FROM tablename
   WHERE date <= '2012-05-03'
   ) as X USING (number)

 

I ran a similar query on one of my tables with 182680 records. Time taken 0.8 seconds.

[Jessica]

Are the indexes on the right columns?

[Illusion]

Try this

 

SELECT t.id, t.date, t.number
FROM tablename as t
WHERE date <= '2012-05-03'
UNION
SELECT t.id, t.date, t.number
FROM tablename as t
INNER JOIN
(
SELECT DISTINCT number FROM tablename
WHERE date <= '2012-05-03'
) as X USING (number)

 

I ran a similar query on one of my tables with 182680 records. Time taken 0.8 seconds.

This results duplicates.

we need to exclude the records which are returned from first query by adding where clause in second query in the union .i.e " where t.date > '2012-05-03' . I still feel like there can be more optimal way to do this by making union as subquery.

[Barand]

UNION will suppress duplicates. You need UNION ALL to get duplicates

[Illusion]

Correct!!!

 

My bad. Time to refresh my SQL skills

Edited January 28, 2013 by Illusion

[peterjc]

Try this

 

SELECT t.id, t.date, t.number
FROM tablename as t
WHERE date <= '2012-05-03'
UNION
SELECT t.id, t.date, t.number
FROM tablename as t
INNER JOIN
(
SELECT DISTINCT number FROM tablename
WHERE date <= '2012-05-03'
) as X USING (number)

 

I ran a similar query on one of my tables with 182680 records. Time taken 0.8 seconds.

 

Tried, and it work.

 

but could i know why need to use UNION? because i tried without union, it will return the same result.

 

SELECT t.id, t.date, t.number
FROM tablename as t
INNER JOIN
       (
       SELECT DISTINCT number FROM tablename
       WHERE date <= '2012-05-03'
       ) as X USING (number)

 

Thanks.

[Barand]

You're right.

 

I set out getting the ones less than the date then finding the rest. It is a bit "belt and braces" as the second bit does indeed find those from the first query also, suppressing duplicates.

 

BTW, how was the execution time? Any freezing?

[peterjc]

You're right.

 

I set out getting the ones less than the date then finding the rest. It is a bit "belt and braces" as the second bit does indeed find those from the first query also, suppressing duplicates.

 

BTW, how was the execution time? Any freezing?

 

No freezing. Forgot the execution time, but i think less than 1 sec.

 

By the way, regarding the sql

 

SELECT *
FROM table
WHERE number IN
(SELECT number
        FROM table
        WHERE date <= '2012-05-03')

 

I think this sql is correct also right? why it is so slow? Is it because of using the "IN" clause that cause mysql to compare with too many records?

 

Thanks.

[Barand]

If you run an EXPLAIN on that query it should tell you that it is using a DEPENDENT SUBQUERY.

 

Those get called for each row in the database and should definitely be avoided on large tables

[peterjc]

If you run an EXPLAIN on that query it should tell you that it is using a DEPENDENT SUBQUERY.

 

Those get called for each row in the database and should definitely be avoided on large tables

 

Ok Barand. By the way, after i got the value and processed in php,

 

NOw, i need to delete or remove all those records.

 

I am using the sql like below, it is a bit slow, any suggestion to improve it?

DELETE FROM table WHERE idtable IN (
SELECT * 
FROM (
SELECT idtable
FROM table AS t
INNER JOIN (
SELECT DISTINCT filename
FROM table
WHERE takendate <=  '2012-05-03'
) AS X
USING ( filename )
) AS p
)

 

 

Thanks in advance.

[Barand]

try same way as the select but with multi-table delete syntax

 

DELETE tablename
FROM tablename
INNER JOIN
(
SELECT DISTINCT number FROM tablename
WHERE date <= '2012-05-03'
) as X USING (number)

Edited January 31, 2013 by Barand