Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 5

Displaying posts with tag: integer (reset)

Understanding B+tree Indexes and how they Impact Performance
+3 Vote Up -0Vote Down
posted by Ovais Tariq on Thu 14 Jul 2011 10:03 UTC
Tags:
(edit) mysql, indexing, performance, indexes, speed, key, disk, children, integer, B+Tree, binary search tree, branching factor, data structure, disk access, height, index value, inner working, leaf node, linked list, lookup, node, page size, range lookup, sequential scan, shallow
Indexes are a very important part of databases and are used frequently to speed up access to particular data item or items. So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to store these indexes, because unless you understand the inner working of an index, you will never be able to fully harness its power.
MySQL: The maximum value of an integer
+10 Vote Up -0Vote Down
posted by Roland Bouman on Tue 04 May 2010 18:15 UTC
Tags:
(edit) mysql, integer
Did you ever have the need to find the maximum value of an integer in MySQL? Yeah, me neither. Anyway, some people seem to need this, and this is what I came up with:

SELECT ~0 as max_bigint_unsigned
,      ~0 >> 32 AS max_int_unsigned
,      ~0 >> 40 AS max_mediumint_unsigned
,      ~0 >> 48 AS max_smallint_unsigned
,      ~0 >> 56 AS max_tinyint_unsigned
,      ~0 >> 1  AS max_bigint_signed
,      ~0 >> 33 AS max_int_signed
,      ~0 >> 41 AS max_mediumint_signed
,      ~0 >> 49 AS max_smallint_signed
,      ~0 >> 57 AS max_tinyint_signed
\G

*************************** 1. row ***************************














  [Read more...]
The actual range and storage size of an INT
+3 Vote Up -2Vote Down
posted by Open Query on Mon 29 Mar 2010 02:45 UTC
Tags:
(edit) mysql, database, data types, int, mariadb, Good practice / Bad practice, integer, birthdate, date-of-birth, year

What’s the difference between INT(2) and INT(20) ? Not a lot. It’s about output formatting, which you’ll never encounter when talking with the server through an API (like you do from most app languages).

The confusion stems from the fact that with CHAR(n) and VARCHAR(n), the (n) signifies the length or maximum length of that field. But for INT, the range and storage size is specified using different data types: TINYINT, SMALLINT, MEDIUMINT, INT (aka INTEGER), BIGINT.

At Open Query we tend to pick on things like INT(2) when reviewing a client’s schema, because chances are that the developers/DBAs are working under a mistaken assumption and this could cause trouble somewhere – even if not in the exact spot where we pick on it. So it’s a case of pattern recognition.

A very practical example of this comes from a client I worked with last

  [Read more...]
Know your data – and your numeric types.
+0 Vote Up -0Vote Down
posted by Open Query on Wed 23 Dec 2009 16:12 UTC
Tags:
(edit) mysql, sql, latitude, longitude, numeric, mariadb, Good practice / Bad practice, integer, accuracy, decimal, double, float, modelling, precision
Numeric types in MySQL have two varieties: - “precise” types such as INTEGER and DECIMAL; - the IEEE-standard floating point types FLOAT and DOUBLE. As a rule of thumb, the first group are for exact, “counted” quantities. The INTEGER types represent whole numbers, and DECIMAL represents “fixed point” decimal, with a preset number of places after the decimal point. Various widths of INTEGER are available in MySQL, from 8-bit TINYINT to 64-bit BIGINT. Calculations with integer types are fast, as they usually correspond to hardware register sizes. DECIMAL is commonly used for quantities like decimal currency where the number of digits of precision is known and fixed. For example, exactly counting pennies in two decimal digits. Computation with DECIMAL is slower than other types, but this is unlikely to impact most applications. In the other category are FLOAT and DOUBLE,  [Read more...]
My favorite MySQL data type – DECIMAL(31,0)
+1 Vote Up -0Vote Down
posted by Ronald Bradford on Fri 18 Sep 2009 23:53 UTC
Tags:
(edit) mysql, General, Databases, Professional, innodb, design, data, Architecture, disk saving, integer

It may seem hard to believe, but I have seen DECIMAL(31,0) in action on a production server. Not just in one column, but in 15 columns just in the largest 4 tables of one schema. The column was being used to represent a integer primary or foreign key column.

In a representative production instance (one of a dozen plus distributed production database servers) the overall database footprint was decreased from ~10 GB to ~2 GB, a 78% saving. In total, 15 columns across just 4 tables were changed from DECIMAL(31,0) to INT UNSIGNED.

One single table > 5GB was reduced to under 1GB (a 81% saving). This being my record for any GB+ tables in my time working with the MySQL database.

Had this server for example had 4GB of RAM, and say 2.5GB allocated to the innodb_buffer_pool_size, this one change moved the system from requiring more consistent disk access (4x

  [Read more...]
Showing entries 1 to 5

Planet MySQL © 1995, 2013, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.