Basics of MySQL — for Beginners

Photo by Boitumelo Phetla on Unsplash

Basics of MySQL — for Beginners

Workcrew's photo
Workcrew
·

6 min read

In this article, we’ll discuss few things you should keep in mind while using MySql for better performance.

Things to keep in mind while designing Schema:

MySql supports a variety of data types but you should always choose which suits your requirement better instead of just making VARCHAR or TEXT to every field xP

Whole Number: there are various types to store integer value and each has its own range of values that can be stored in int.

  • TINYINT: 8bit

  • SMALLINT: 16bit

  • MEDIUMINT: 24bit

  • INT: 32bit

  • BIGINT: 64bit

Each type can store values from -2^(n-1) to 2^(n-1) -1, so it is better to choose wisely because every type takes a different amount of space to store values.

And if you are pretty confident about there are no negative values then you can actually use UNSIGNED to increase the range by twice to it 0 to 2 * (2^n-1) with the same space.

Interesting Fact: MySql allows us to define the size of int less than 8 bit too, something like this int(1) but internally it is still take space as normal int instead 1 bit, it is MySQL client level check.

Real Number (FLOAT vs DECIMAL): you should use FLOAT as much as possible for storing fractional values until you really need exact fractional values because FLOAT takes 32bit and DECIMAL takes 64bit space.

When you really care about exact fractional points you should use DECIMAL.

String: there are mainly two data types in MySql which look pretty similar but have lots of differences

  • VARCHAR: VARCHAR is more efficient in terms of space than the fixed-length type because it uses space only as much it needs like if you defined VARCHAR(20) it doesn’t mean it is always going to take 20 bytes for each row, it uses the space according to the value stored in the row like if you are storing a string of length 10 then it is going to take 10bytes of space, so it is clear that 20 is the maximum length of a string can have.

VARCHAR actually improves performance in terms of space because of variable space but sometimes it leads to the fragmentation problem when we update the value which has a greater length than the previously-stored value so MySQL has to reallocate the space to that row which could lead to fragmentation.

  • CHAR: you should only use CHAR only when you are pretty confident about the values going to stored in it has fixed length or nearly similar length because CHAR is always going to take the fixed about of space, unlike VARCHAR.

Like if you defined CHAR(20) then it is always going to take 20bytes of space for each row no matter what length of string you are going to store.

Interesting Fact: VARCHAR actually takes few extra bytes to store the length of string too than the size defined at schema creation like if you defined VARCHAR(100) then it’ll going to take 1 extra byte, 2byte for VARCHAR(1000) to store the length of the string too, while CHAR always take the exact amount of space like CHAR(100) takes exact 100byte no extra space.

BINARY vs VARBINARY: when you’re storing binary strings you should use BINARY or VARBINARY instead VARCHAR because everything in memory so binary comparisons are faster than the character comparisons.

BLOB vs TEXT: you should only use them when you really have to store a large amount of binary string or character string respectively.

DATETIME vs TIMESTAMP: you should use TIMESTAMP as much as you can because TIMESTAMP takes 4bytes while DATETIME takes 8bytes of space. DATETIME can hold values from 1001 to 9999 years while TIMESTAMP can hold values from 1970 to 2038 years.

Interesting Fact:

if you don’t specify the value for the TIMESTAMP column while inserting it’ll automatically set to the current timestamp.

If you store 0 to column define as TIMESTAMP, the stored value will look something like this 1969–12–31 19:00:00

MySql Indexes

Indexes are used to improve the lookup for the MySQL queries to find the data as fast as possible from a huge amount of data, MySQL has various types of index, each has its own properties which decide when to use which one.

B-Tree Indexes: B-Tree indexes are the most common index in MySql, B-Tree actually stores the information in the order that’s why it is mandatory to carefully choose the order of columns while creating a B-Tree index. Since information is stored in an order which makes searching pretty fast while most of out queries fetching the data in the same order.

You can understand it this way like we have the following table:

CREATE TABLE People ( name varchar(50) not null, dob date not null, gender enum(‘m’, ‘f’)not null, key(name));

So now all the recorder are stored in sorted order by name column so it is pretty fast to search for all people whose name start with Alex.

When to use this:

  • Prefix Matching: Since records are stored in order so it is efficient to search for prefix, like all the people whose name starts with the given prefix.

  • Range Value: When you have to search for records in range, like all the people from Alex to Kim.

  • Order By: When your queries mostly use ORDER BY, the index makes it efficient because records are already stored in order.

Things to keep in mind while creating:

  • Column Order: Column order is very important for B-Tree indexes because data stored in order, so let support you crated an index with KEY(first_name, last_name) and if you search for all people whose last_name start with John, the index won’t gonna help you because you skip the column first_name, so the index is only gonna use when you search the info in the same order of the index.

Hash Indexes: Hash index is built on the top of the hash table which is helpful for exact lookup, so what MySql does is when we create a Hash index MySql compute the hash code and link a pointer to the equivalent row to it for faster lookups.

When to use this:

  • Exact Matching: Hash index only helpful when you have queries for exact lookups.

Things to keep in mind:

  • Hash Index does not help in range queries.

  • Hash Index does not work for partial key matching.

Interesting Facts:

If you have an expression in the WHERE clause of your query then it won’t gonna use index, so make sure you don’t use expressions in WHERE clause.

SELECT * FROM USERS WHERE id + 1 = 3;

When you have to create an index that contains very long string so you can use prefix of strings instead whole string while creating an index to make your index efficient in terms of space and time.

Query Optimizations:

  • Always make sure you retrieve only rows you need and avoid * as much as possible while selecting columns.

  • Instead of running one massive query, run a bunch of short queries so it won’t lock so many rows for a long time.

  • Use IN instead of JOIN whenever you can because IN is more efficient in caching than JOIN.

  • Use Indexes whenever you can.

Interesting Facts:

Searching in IN clause takes log(n) in InnoDB engine.

Connection between the MySql Client and DataBase is Half Duplex. So when one side send data other side has to recieve entire message before responding.

DatabasesMySQL