I have a table named test :
create table demo (name varchar(10), mark1 int, mark2 int);
I need the total of mark1 and mark2 for each row many times.
select name, (mark1 + mark2) as total from demo;
Which I am told is not efficient. I am not allowed to add a new total column in the table.
Can I store such business logic in Index?
I created a view
CREATE VIEW view_total AS SELECT name, (mark1 + mark2) as 'total' from demo;
I populated the demo table with:
DELIMITER $$ CREATE PROCEDURE InsertRand(IN NumRows INT) BEGIN DECLARE i INT; SET i = 1; START TRANSACTION; WHILE i <= NumRows DO INSERT INTO demo VALUES (i,i+1,i+2); SET i = i + 1; END WHILE; COMMIT; END$$ DELIMITER ; CALL InsertRand(100000);
The execution time of
select sum(mark1 + mark2) from view_total;
select sum(total) from demo;
is same, 10 ms. So I have not gained any benefit of view. I tried to create index over the view with :
create index demo_total_view on view_total (name, total);
which failed with error :
ERROR 1347 (HY000): 'test.view_total' is not BASE TABLE
Any pointer about how do I prevent the redundant action of totaling the columns?