1

I have a select that returns something that looks like the following:

PeopleId Timestamp FavoriteNumber
1 2020-11-04 17:40:33 14
2 2020-12-06 21:19:41 59
3 2020-12-10 18:46:11 14
4 2020-12-15 00:01:53 7

Given this information, I want to, for every row, check if the person's favorite number is the first appearance of that number and if so return 0, if not, return That would make a table that looks like:

PeopleId Timestamp FavoriteNumber HasOriginalNumber
1 2020-11-04 17:40:33 14 1
2 2020-12-06 21:19:41 59 1
3 2020-12-10 18:46:11 14 0
4 2020-12-15 00:01:53 7 1

Assume the query up to this point has been:

Select p.PeopleId, p.Timestamp, p.FavoriteNumber From people p

The second table could be achieved by doing:

Select p.PeopleId, p.Timestamp, p.FavoriteNumber, 
    ((Select Min(x.Timestamp) from people x where x.FavoriteNumber = p.FavoriteNumber) = p.TimeStamp) as 
    HasOriginalColor)
    From people p

However, I think that query will be very inefficient for large tables and seems like overkill. Is there an easier and faster way to do this?

2
  • Critical - what version of MySQL are you running? Also, in future, could you please provide a fiddle of your table structures (CREATE TABLE...) and data (INSERT INTO...) - this helps eliminate error and duplication of effort of behalf of those trying to help you! Help us to help you! – Vérace Jan 10 at 8:13
  • Buginga, If you expect your application to still be used in 2038, you may wish to consider an alternative datatype. 2038 is not that far away. Check documentation for date range lower and upper limit. – Wilson Hauck Jan 12 at 17:50
1

This is a classic example of where window functions come in handy! In this case, we shall use the ROW_NUMBER() function.

To answer your question, I did the following (see fiddle here):

The table:

CREATE TABLE people 
(
  people_id INT, 
  t_timestamp TIMESTAMP, 
  favourite_number INT
);

Just a word of advice, you should NEVER call your fields (or your tables) using SQL keywords - it makes debugging more complex and error-prone and your apps less portable and it's bad practice generally.

Populate it:

INSERT INTO people VALUES 
(1, '2020-11-04 17:40:33',  14),
(2, '2020-12-06 21:19:41',  59),
(3, '2020-12-10 18:46:11',  14),
(4, '2020-12-15 00:01:53',  7);

Then run the query (I'll go through the logic step-by-step):

SELECT 
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
  people_id,
  t_timestamp,
  favourite_number
FROM people
ORDER BY people_id, t_timestamp, favourite_number;

Result:

rn  people_id   t_timestamp favourite_number
1   1   2020-11-04 17:40:33 14
1   2   2020-12-06 21:19:41 59
2   3   2020-12-10 18:46:11 14
1   4   2020-12-15 00:01:53 7

So, we can see by inspection that we want all of the records where rn = 1.

We now have to do a SUBSELECT (you can't put window functions in WHERE clauses):

SELECT * FROM
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
    people_id,
    t_timestamp,
    favourite_number
  FROM people
--  ORDER BY people_id, t_timestamp, favourite_number
) AS tab
WHERE rn = 1
ORDER BY favourite_number, t_timestamp, favourite_number;

Result:

rn  people_id   t_timestamp          favourite_number
1   4           2020-12-15 00:01:53                 7
1   1           2020-11-04 17:40:33                14
1   2           2020-12-06 21:19:41                59

Now, these are the records we're interested in, but it's not quite the result you want.

We obtain this by using a CASE function, and since we're SELECTing all records, we can eliminate the SUBSELECT:

SELECT 
  people_id,
  t_timestamp,
  favourite_number,
    CASE
      WHEN 
        ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) = 1
        THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn
FROM people
ORDER BY people_id;

Result:

people_id          t_timestamp favourite_number has_original_number rn
        1  2020-11-04 17:40:33               14                   1  1
        2  2020-12-06 21:19:41               59                   1  1
        3  2020-12-10 18:46:11               14                   0  2
        4  2020-12-15 00:01:53                7                   1  1

Which, excepting rn is the result you want!

This is an example of a class of problems known as greatest-n-per-group - it's a dba.stackexchange tag - look at some of the questions in there and I would strongly urge you to get to know window functions - they will repay any effort you put into them many times over!

I modified your query in this way:

SELECT 
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT 
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p;

The result is the same!

Performance analysis: (MySQL)

I then looked at the performance of both queries using the new EXPLAIN ANALYZE <followed by query text> functionality available in MySQL version >= 8.18 (as explained here and here). For brevity, I'm only showing the plans - see the revised fiddle for the whole thing. I also make use of the MySQL profiling capability as explained here.

(caveat: I am not an expert on query plans - but I think I know a bit - any corrections, references, URLs appreciated):

I turned on SET PROFILING = 1;

Then, after running the two queries, I looked at the profiles (some of the query text is truncated):

SHOW PROFILES; 

=================================

Query_ID    Duration    Query

1           0.00078575  

SELECT 
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT 
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p
ORDER BY p.people_id

=======================================

2           0.00062000  

SELECT 
  p.people_id,
  p.t_timestamp,
  p.favourite_number,
    CASE
      WHEN 
        ROW_NUMBER() OVER (PARTITION BY p.favourite_number ORDER BY p.t_timestamp) = 1
      THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp)

_

I ran this several times (typical run shown) and the result consistently shows that the ROW_NUMBER() query is faster (by ~ 20% - frequently more) than the MIN() one - this seems logical (to me) since the MIN() query is SELECTing from the people table twice! The difference is larger if I run the MIN() query second. This effect is also seen if the queries are run in the opposite order.

However, when I ran the query plans, I got a surprise:

-> Sort: people.people_id  (actual time=0.064..0.065 rows=4 loops=1)
    -> Table scan on <temporary>  (actual time=0.000..0.001 rows=4 loops=1)
        -> Temporary table  (actual time=0.057..0.058 rows=4 loops=1)
            -> Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.043..0.049 rows=4 loops=1)
                -> Table scan on <temporary>  (actual time=0.000..0.001 rows=4 loops=1)
                    -> Temporary table  (actual time=0.040..0.042 rows=4 loops=1)
                        -> Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.031..0.036 rows=4 loops=1)
                            -> Sort: people.favourite_number, people.t_timestamp  (cost=0.65 rows=4) (actual time=0.027..0.027 rows=4 loops=1)
                                -> Table scan on people  (cost=0.65 rows=4) (actual time=0.013..0.016 rows=4 loops=1)

2nd Query (yours modified):

-> Table scan on p  (cost=0.65 rows=4) (actual time=0.013..0.015 rows=4 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: min(x.t_timestamp)  (actual time=0.036..0.037 rows=1 loops=4)
        -> Filter: (x.favourite_number = p.favourite_number)  (cost=0.35 rows=1) (actual time=0.009..0.033 rows=2 loops=4)
            -> Table scan on x  (cost=0.35 rows=4) (actual time=0.008..0.031 rows=4 loops=4)

The performances are roughly comparable - a bit of variability from run to run.

However, because you're returning the whole table, the optimiser will ignore indexes and scan the whole table - Table Scan on people - note that for your query, it performs 2 Table Scans whereas for mine it only performs 1 for mine (temporary tables excluded - in memory?).

IMHO, this means that as the row-count of the people table grows, the query which performs 2 table scans will slow down as a function of O(n2) but the other query will only slow down as a function of O(n). So, long-term, I think you'll be better off with my query - but, I am open to correction on this!

1
  • What a well written and helpful answer, thank you! – Bubinga Jan 11 at 3:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.