0

I have a table which record the bandwidth usage in MySQL. Every minute I have a new record. Here is my table :

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| IfInOctets       | varchar(255) | YES  |     | NULL    |       |
| IfOutOctets      | varchar(255) | YES  |     | NULL    |       |
| inDiff           | varchar(255) | YES  |     | NULL    |       |
| outDiff          | varchar(255) | YES  |     | NULL    |       |
| time             | varchar(255) | YES  |     | NULL    |       |
| timeDiff         | varchar(255) | YES  |     | NULL    |       |
| inDiffFormatted  | varchar(255) | YES  |     | NULL    |       |
| outDiffFormatted | varchar(255) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

I think I need to use integer instead of varchar but I'm not sure about that.

Here is a sample data :

+------------+-------------+------------+------------+------------+------------+--------------------+--------------------+
| IfInOctets | IfOutOctets | inDiff     | outDiff    | time       | timeDiff   | inDiffFormatted    | outDiffFormatted   |
+------------+-------------+------------+------------+------------+------------+--------------------+--------------------+
| 2147483647 | 1376607409  | 2460390830 | 1376607409 | 1556176316 | 1556176316 | 1.2062445720411E-5 | 6.7490302544245E-6 |
| 2147483647 | 1376607409  | 0          | 0          | 1556176321 | 5          | 0                  | 0                  |
| 2147483647 | 1376828650  | 30146      | 221241     | 1556176381 | 60         | 0.003833262125651  | 0.028132247924805  |
| 2147483647 | 1377086102  | 27318      | 257452     | 1556176442 | 61         | 0.003416718029585  | 0.032200047227203  |
| 2147483647 | 1377214273  | 26182      | 128171     | 1556176501 | 59         | 0.003385640807071  | 0.016574019092624  |
| 2147483647 | 1377750582  | 17235      | 536309     | 1556176561 | 60         | 0.0021915435791016 | 0.068195215861003  |
| 2147483647 | 1378643334  | 56369      | 892752     | 1556176621 | 60         | 0.0071676890055339 | 0.11351928710938   |
| 2147483647 | 1384687953  | 168712     | 6044619    | 1556176681 | 60         | 0.021452840169271  | 0.76861305236816   |
| 2147483647 | 1385607549  | 132968     | 919596     | 1556176742 | 61         | 0.016630579213627  | 0.11501574907147   |
| 2147483647 | 1386144703  | 86602      | 537154     | 1556176801 | 59         | 0.011198658054158  | 0.069460335424391  |
| 2147483647 | 1386706605  | 36223      | 561902     | 1556176861 | 60         | 0.0046059926350911 | 0.071449534098307  |
| 2147483647 | 1387068552  | 78973      | 361947     | 1556176921 | 60         | 0.010041936238607  | 0.046023941040039  |
| 2147483647 | 1388695622  | 127944     | 1627070    | 1556176981 | 60         | 0.016268920898438  | 0.20689264933268   |
| 2147483647 | 1389068366  | 140436     | 372744     | 1556177041 | 60         | 0.017857360839844  | 0.047396850585937  |
| 2147483647 | 1389279305  | 68037      | 210939     | 1556177101 | 60         | 0.0086513519287109 | 0.026822280883789  |
| 2147483647 | 1389417338  | 46582      | 138033     | 1556177162 | 61         | 0.0058261058369621 | 0.017264069103804  |
| 2147483647 | 1389589892  | 37450      | 172554     | 1556177221 | 59         | 0.004842725850768  | 0.0223132634567    |
| 2147483647 | 1390475078  | 62933      | 885186     | 1556177281 | 60         | 0.0080023447672526 | 0.11255722045898   |
| 2147483647 | 1391155868  | 83365      | 680790     | 1556177341 | 60         | 0.010600407918294  | 0.086566925048828  |
| 2147483647 | 1392049156  | 68532      | 893288     | 1556177401 | 60         | 0.0087142944335938 | 0.11358744303385   |
| 2147483647 | 1392862166  | 108235     | 813010     | 1556177462 | 61         | 0.013537172411309  | 0.10168482045658   |
| 2147483647 | 1392960189  | 10691      | 98023      | 1556177521 | 59         | 0.0013824721514168 | 0.012675527798927  |
| 2147483647 | 1393207096  | 18882      | 246907     | 1556177581 | 60         | 0.0024009704589844 | 0.031395848592122  |
| 2147483647 | 1393448878  | 13595      | 241782     | 1556177641 | 60         | 0.0017286936442057 | 0.030744171142578  |
| 2147483647 | 1408075956  | 288098     | 14627078   | 1556177701 | 60         | 0.036633555094401  | 1.8599291483561    |
| 2147483647 | 1427800637  | 344325     | 19724681   | 1556177762 | 61         | 0.043065430688076  | 2.4670061205254    |
| 2147483647 | 1449947549  | 315400926  | 22146912   | 1556177821 | 59         | 40.785052541959    | 2.8638564287606    |
| 2147483647 | 1470058156  | 315793135  | 20110607   | 1556177882 | 61         | 39.496892084841    | 2.5152746732118    |
| 2147483647 | 1489563366  | 316133487  | 19505210   | 1556177941 | 59         | 40.879781302759    | 2.5222532627946    |
| 2147483647 | 1511595700  | 316511799  | 22032334   | 1556178002 | 61         | 39.58677685847     | 2.7556289922996    |
+------------+-------------+------------+------------+------------+------------+--------------------+--------------------+
  • All fields must be of numeric types. FLOAT or DOUBLE for last 2 fields, and INT or BIGINT (dependent by theoretical maximal value) for all another. If last 2 fields can be calculated from all another field's values, they must be removed at all. – Akina Apr 25 at 8:01
  • I don't really understand why I should delete the last column. I don't really know the max value it can get – executable Apr 25 at 8:04
  • When some field value can be calculated from another field's values, it must be removed, and its value must be calculated in the query where it needed. Exclusions are too rare, and only in very special cases. – Akina Apr 25 at 8:07
  • The query would take more time to calculate ? – executable Apr 25 at 8:10
  • Yes. But the time difference is less than time compare accuracy. – Akina Apr 25 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.