CODEWITHSUNDEEP
mysqlquery-optimization
ntg
ntg

Reputation: 61

mysql calculation optimization

I have a column in a table that needs to hold a 'positive' and 'negative' value or 'no value'. I have to sum millions of rows very! often and display it to users. My question is: Which sum operation will be quicker.

  1. If I mark 'no value' with 0, 'positive' with 1 and 'negative' with -1
  2. If i mark 'no value' with 0, 'positive' with 1 and 'negative' with 2

My preferred option is 1. because it will save me the effort of additional php handling and will display the correct result right away. The question is weather using a sign (-1,0,+1) in a db will slow it down or its the same as if i don't use a sign (0,1,2). Keep in mind that i will have millions of rows and they will be summed up regularly. Thank you.

Upvotes: 0

Views: 97

Answers (1)

xception
xception

Reputation: 4287

1) as it will give you direct results!

No, a sign does not impact performance as long as the type for the column is defined as a signed int or real...

Your question should have been about datatype, an int is preffered, less favorable is a float and least favored a string, although mysql can sum strings the conversion will be slow.

Also, assuming this table just holds some cross reference between two other tables and an up/down vote, it would be a hell of a lot faster if you just removed all the 0 values and inserted rows only when a vote is cast. And you could make it even faster if you were caching the values and using triggers to update the cache column referenced table whenever a vote is cast/uncast.

A fiddle example to update automatically vote sums, http://sqlfiddle.com/#!2/044c3/1

Upvotes: 1

Related Questions