0

I have a Reporting table where i store description

tableA

sno   | Project |name     | description      | mins |
1     | prjA    |nameA    |ABC -10% task done|  30  |
...
3000  | prjA    |nameB    |ABC -70% task done|  70  |

i want to query the description field and save in another table

tableB

id | valueStr | total_mins  | last_sno
1  |  ABC     | 100         | 3000

if there is no entry in second table , i create a entry with default values

if there is and entry in second table , i update 2nd table , with the total_mins and increment the last_sno to that value say 3300 , so that the next time i query this table i get values from second table and based on the last_sno

Query

SELCT last_sno FROM tableB where valueStr ='ABC'

the first 3 characters in the description field

SELECT max(sno), sum(mins) FROM tableA
 where sno > last_sno and description like 'ABC%'

Since the first table has million of rows so, i search the first table with sno > last_sno , so that should help performance right ?

but the explain shows that it scans the same no of rows , when i query the first table from the first sno

1 Answer 1

2

Assuming that valueStr has a Unique constraint on the second table, so the following query will always yield at most one row:

SELECT last_sno 
FROM tableB 
WHERE valueStr ='ABC'

You can use it as a subquery (or a derived table) to run an INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO tableB 
  (valueStr, total_mins, last_sno)
SELECT 
  'ABC', SUM(a.mins), MAX(a.sno)
FROM tableA AS a
WHERE a.sno > COALESCE(
          ( SELECT last_sno 
            FROM tableB 
            WHERE valueStr ='ABC'
          ), 0) 
  AND a.description LIKE 'ABC%'
ON DUPLICATE KEY UPDATE
  total_mins = total_mins + VALUES(total_mins),
  last_sno = VALUES(last_sno) ;

Regarding efficiency, the condition you have

WHERE a.sno > @last_sno
  AND a.description LIKE 'ABC%'

is basically two range conditions, one on sno and one on description (think of description LIKE 'ABC%' the same as description >= 'ABC' AND description < 'ABD') which is rather hard to optimize. The optimizer will have to choose one index to use, it can't use both indexes for this query.

If you could split the description column or add one more with the first (three or more) characters of description that uniquely identify the value_str, making tableA something like this:

tableA

sno   | Project |name     | description      | value_str | mins |
1     | prjA    |nameA    |ABC -10% task done| ABC       | 30   |
...
3000  | prjA    |nameB    |ABC -70% task done| ABC       | 70   |

then you could write the conditions as:

WHERE a.sno > @last_sno
  AND a.value_str = 'ABC'

and a composite index on (value_str, sno) would make this and similar queries much more efficient than what you have now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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