MySQL: Computed Column

by

A computed column normally means a value you can calculate per row. MySQL does not support that, but SQL Server does. For example, to store the sum of two columns permanently:

create table Table1 (a int, b int, c as a+b persisted)

However, you’re looking to store an aggregate, that is, a value for a group of rows. MySQL and SQL Server don’t support materialized views with an aggregate, but Oracle does:

create table Table1 (a int, b int);

create materialized view View1 as
select  a
,       count(*) as Cnt
from    Table1
group by
    a;

With MySQL however, the closest you can do is a cronjob that periodically populates a table:

truncate table Table1Summary;
insert Table1Summary (a, Cnt) select a, count(*) from Table1;

You can query the table like a materialized view; it will be as fast, but not guaranteed to be up to date.

More Related Contents:

  1. Which DB technology is sued to solve the following search issue? [closed]
  2. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  3. How to select the nth row in a SQL database table?
  4. 1052: Column ‘id’ in field list is ambiguous
  5. How to do a batch insert in MySQL
  6. Count the occurrences of DISTINCT values
  7. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  8. Storing JSON in database vs. having a new column for each key
  9. How do I restore a dump file from mysqldump?
  10. Cast int to varchar
  11. Import SQL file into mysql
  12. How to change the default port of mysql from 3306 to 3360
  13. Does mysql have the equivalent of Oracle’s “analytic functions”?
  14. How to skip certain database tables with mysqldump?
  15. “where 1=1” statement [duplicate]
  16. How to set a MySQL row to READ-ONLY?
  17. MySQL: LAST_INSERT_ID() returns 0
  18. Using reserved words in column names
  19. Calculating a Moving Average MySQL?
  20. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  21. How to insert a file in MySQL database?
  22. Strings as Primary Keys in MYSQL Database [closed]
  23. MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint
  24. What is the difference between SQL and MySQL? [closed]
  25. How to speed up sql queries ? Indexes?
  26. Selecting distinct 2 columns combination in mysql
  27. How to store a one to many relation in MySQL database?
  28. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;
  29. Optimistic locking in MySQL
  30. mySQL SELECT upcoming birthdays

Leave a Comment