MySQL - Add flag column to identify the first payment

user_4053069 0
Viewed 90

I want to improve my current query. So I have this table called Incomes. Where I have a sourceId varchar field. I have a single SELECT for the fields I need, but I needed to add an extra field called isFirstTime to represent if it was the first time on the row on what that sourceId was used. This is my current query:

SELECT DISTINCT 
  `income`.*,
  CASE WHEN (
      SELECT 
        `income2`.id
      FROM 
          `income` as `income2`
      WHERE
          `income2`."sourceId" = `income`."sourceId"
      ORDER BY 
          `income2`.created asc
      LIMIT 1
      ) = `income`.id THEN true ELSE false END
  as isFirstIncome
FROM 
  `income` as `income` 
WHERE `income`.incomeType IN ('passive', 'active') AND `income`.status = 'paid'
ORDER BY `income`.created desc
LIMIT 50

The query works but slows down if I keep increasing the LIMIT or OFFSET. Any suggestions?

UPDATE 1: Added WHERE statements used on the original query

UPDATE 2: MYSQL version 5.7.22

mysql sql
2 Answers

You can achieve it using Ordered Analytical Function.

You can use ROW_NUMBER or RANK to get the desired result.

Below query will give the desired output.

SELECT *,
       CASE
         WHEN Row_number()
                OVER(
                  PARTITION BY sourceid
                  ORDER BY created ASC) = 1 THEN true
         ELSE false
       END AS isFirstIncome
FROM   income 
WHERE incomeType IN ('passive', 'active') AND status = 'paid' 
ORDER BY created desc

DB Fiddle: See the result here

user_140485110

My first thought is that isFirstIncome should be an extra column in the table. It should be populated as the data is inserted.

If you don't like that, let's try to optimize the query...

Let's avoid doing the subquery more than 50 times. This requires turning the query inside-out. (It's like "explode-implode", where the query gathers lots of stuff, then sorts it and throws most of the rows away.)

To summarize:

  1. do the least amount of effort to just identify the 5 rows.

  2. JOIN to whatever tables are needed (including itself if appropriate); this is to get any other columns desired (including isFirstIncome).

    SELECT i3.*, 
       ( ... using i3 ... )  as isFirstIncome
   FROM (
     SELECT i1.id, i1.sourceId
         FROM  `income` AS i1
         WHERE  i1.incomeType IN ('passive', 'active')
           AND  i1.status = 'paid'
         ORDER BY  i1.created DESC
         LIMIT  50
        ) AS i2
   JOIN income AS i3  USING(id)
   ORDER BY i2.created DESC    -- yes, repeated

(I left out the computation of isFirstIncome; it is discussed in other Answers. But note that it will be executed at most 50 times.)

(The aliases -- i1, i2, i3 -- are numbered in the order they will be "used"; this is to assist in following the SQL.)

To assist in performance, add

INDEX(status, incomeType, created, id, sourceId)

It should help with my formulation, but probably not for the other versions. Your version would benefit from

INDEX(sourceId, created, id)
user_17668310
Related