It is too often that developers use the wrong data types...

It is too often that developers use the wrong data types in their databases. As queries to databases take a major amount of time in any application, it is essential that the database and it's tables are as optimised as possible to ensure the best efficiency, especially in high-demand applications.

Oh! Hoping to help alter the overarching vibe of my old site. Ideas on the modern feel at https://bedbugscalgary.ca? Absolutely a gifted Calgary based bed bug treatment service whenever looking in the whole Alberta vicinity. Make a remark. Many thanks!

In MySQL, the most simple optimisation is to select the correct, most appropriate, and therefore most efficient data types. But many MySQL developers, and other database developers, sometimes fail to do this; non-MySQL developers - such as PHP programmers who use MySQL as just a necessity for their database-driven website - often fail miserably.

To help you understand the data types available, and hopefully use them correctly, I have written about some of the most common - the ones which you probably use every day:

SIGNED or UNSIGNED?

For people who don't know what signed or unsigned is, I'll give a brief explanation:

With signed integers, one bit is reserved so the numbers can be made negative. With unsigned, a bit is not reserved and numbers can only be positive. If you had a SIGNED INT, the value can be between -2,147,483,648 and 2,147,483,647. But, with an UNSIGNED INT, the value can range from 0 to 4,294,967,295.

If you will never use a negative number in a field, then you should use UNSIGNED. If you have an auto-incrementing primary key, then you'd want to use UNSIGNED because it allows for more rows.

Too big?

You should think carefully before creating fields. If you have a ' users ' table on a small website, with UNSIGNED INT as the primary key, then ask yourself, "Will I ever get 4,294,967,295 users?" The most likely answer to this question is no. You've got to think about what the highest value is ever likely to be. Then, select the right numeric data type which can cover that amount. I've made a little numeric data type table with the minimum and maximum values:

MinimumMaximumSIGNED TINYINT-128127UNSIGNED TINYINT0255SIGNED SMALLINT-32,76832,767UNSIGNED SMALLINT065,535SIGNED MEDIUMINT-8,388,6088,388,607UNSIGNED MEDIUMINT016,777,215SIGNED INT-2,147,483,6482,147,483,647UNSIGNED INT04,294,967,295SIGNED BIGINT-9,223,372,036,854,775,8089,223,372,036,854,775,807UNSIGNED BIGINT018,446,744,073,709,551,615

What's the difference between CHAR and VARCHAR?

The difference between CHAR and VARCHAR is that CHAR is fixed length, while VARCHAR is var iable length. This means that CHAR is always the same size and takes up the same amount of bytes, while VARCHAR varies. VARCHAR also adds an extra byte. When should you use CHAR?

To store MD5 passwords, which are always 32 characters long ( when stored in hexadecimal form; it may be better to store them as 16 bytes in a BINARY field? ).

To store same-length barcodes.

When should you use VARCHAR?

To store email addresses, which vary in size

To store peoples names, which vary in size

You'd usually want to use VARCHAR when storing most user input, because their input can vary in size.

Store as numbers

When possible, you should store data in numbers, because it can be searched and ordered more quickly. For example, IP address can be stored like this:

123.123.123.123

using VARCHAR(15). If you first translated it to an IPv4 Internet network address (in PHP, you can use the ip2long() function), you could store it as:

2071690107

using a SIGNED INT.

Choosing the right date/time type

In MySQL, there are 5 different date and time types:

DATE

TIME

DATETIME

TIMESTAMP

YEAR

DATE gives the date to the nearest day, TIME gives the time (but not the date) to the nearest second, DATETIME gives the date and time to the nearest second, TIMESTAMP gives the number of seconds since epoch, and YEAR gives the date to the nearest year.

YEAR only requires 1 byte of storage, so is the best option if you only need to know the year.

DATE and TIME are the next smallest type of date/time storage, requiring just 3 bytes.

TIMESTAMP requires 4 bytes.

DATETIME requires 8 bytes.

In almost any case, it is probably best to use TIMESTAMP, which gives the date and time to the nearest second.

Thx people! I just messaged my relation that I would absolutely reference her good eurovan rental service in Vancouver onlines, https://vancouverwestyrentals.com/ through a suitable piece. If you are looking to get a eurovan camper rental service inside of the outlying British Columbia region, they truly are very good.

Ultimately, I absolutely will remark the basic outline to this unique text was granted via Brian at epolos. They're an impressive clothing companys. We always treasure a first-rate proposal!

Notes

https://getthemortgagethatgives.ca - Davis, you came through in my situation once again.

Posted in Cleaning Services Post Date 10/25/2019


Comments

Name

Email

Website

Comment