在MySQL中明智的SUM VS COLUMN明智的SUM

Question

I have a tableA this contains the following structure

I modified this structure into tableB like below to reduce number of rows and the category is fixed length

Assume I have 21 lakh data in tableA after modified into new structure tableB contains 70k rows only

In some case I want to SUM all the values into the table,

QUERY1: SELECT SUM(val) AS total FROM tableA;

vs

QUERY2: SELECT SUM(cate1+cate2+cate3) AS total FROM tableB;

QUERY1 is executing faster while comparing to QUERY2.

tableB contains less rows while comparing to tableA

As of my expectation QUERY2 is faster but QUERY1 is the fastest one.

Help me to understand why the performance is reduced in QUERY2?

Answer 1

MySQL is optimized to speed up relational operations. There is not so much effort at speeding up the other kinds of operations MySQL can perform. Cate1+Cate2+Cate3 is a perfectly legitimate operation, but there's nothing particularly relational about it.

Table1 is actually simpler in terms of the relational model of data than Table2, even though Table1 has more rows. It's worth noting in passing that Table1 conforms to first normal form but Table2 does not. Those three columns are really a repeating group even though it's been made to look like they are not.

So First Normal form is good for you in terms of performance (most of the time).