MySQL Table Types, or Storage Engines

It is essential to understand the features of each table type in MySQL so that you can use them effectively to maximise the performance of your databases.

MySQL provides various storage engines for its tables as below:

  • MyISAM
  • InnoDB
  • MERGE
  • MEMORY (HEAP)
  • ARCHIVE
  • CSV
  • FEDERATED

Each storage engine has its own advantages and disadvantages. It is crucial to understand each storage engine features and choose the most appropriate one for your tables to maximize the performance of the database.

MyISAM

The MyISAM tables are optimised for compression and speed. MyISAM tables are also portable between platforms and operating systems.

The size of MyISAM table can be up to 256TB, which is huge. In addition, MyISAM tables can be compressed into read-only tables to save spaces. At startup, MySQL checks MyISAM tables for corruption and even repairs them in a case of errors. The MyISAM tables are not transaction-safe.

Before MySQL version 5.5, MyISAM is the default storage engine when you create a table without specifying the storage engine explicitly. From version 5.5, MySQL uses InnoDB as the default storage engine.

InnoDB

The InnoDB tables fully support ACID-compliant and transactions. They are also optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-forward operations. The size of an InnoDB table can be up to 64TB.

Like MyISAM, the InnoDB tables are portable between different platforms and operating systems. MySQL also checks and repairs InnoDB tables, if necessary, at startup.

MERGE

A MERGE table is a virtual table that combines multiple MyISAM tables that have a similar structure into one table. The MERGE storage engine is also known as the MRG_MyISAM engine. The MERGE table does not have its own indexes, it uses indexes of the component tables instead.

Using MERGE table, you can speed up performance when joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables. If you use DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying tables will not be affected.

Memory

The memory tables are stored in memory and use hash indexes so that they are faster than MyISAM tables. The lifetime of the data of the memory tables depends on the uptime of the database server. The memory storage engine is formerly known as HEAP.

Archive

The archive storage engine allows you to store a large number of records, which for archiving purpose, into a compressed format to save disk space. The archive storage engine compresses a record when it is inserted and decompress it using the zlib library as it is read.

The archive tables only allow INSERT and SELECT statements. The ARCHIVE tables do not support indexes, so it is required a full table scanning for reading rows.

CSV

The CSV storage engine stores data in comma-separated values (CSV) file format. A CSV table brings a convenient way to migrate data into non-SQL applications such as spreadsheet software.

CSV table does not support NULL data type. In addition, the read operation requires a full table scan.

FEDERATED

The FEDERATED storage engine allows you to manage data from a remote MySQL server without using cluster or replication technology. The local federated table stores no data. When you query data from a local federated table, the data is pulled automatically from the remote federated tables.

Clustered and Secondary Indexes

InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimise the most common lookup and DML operations for each table.

  • When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
  • If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
  • If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
How the Clustered Index Speeds Up Queries

Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organisations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)

How Secondary Indexes Relate to the Clustered Index

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

 

MySQL INDEXES

Database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns.

While creating index, it should be considered that what are the columns which will be used to make SQL queries and create one or more indexes on those columns.

Practically, indexes are also type of tables, which keep primary key or index field and a pointer to each record into the actual table.

The users cannot see the indexes, they are just used to speed up queries and will be used by Database Search Engine to locate records very fast.

INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to insert or update index values as well.

Simple and Unique Index:

You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Here is the syntax to create an Index on a table

You can use one or more columns to create an index. For example, we can create an index on authors_tbl using author_name.

You can create a simple index on a table. Just omit UNIQUE keyword from the query to create simple index. Simple index allows duplicate values in a table.

If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name.

ALTER command to add and drop INDEX:

There are four types of statements for adding indexes to a table:

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.
  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): This statement creates an index for which values must be unique (with the exception of NULL values, which may appear multiple times).
  • ALTER TABLE tbl_name ADD INDEX index_name (column_list):This adds an ordinary index in which any value may appear more than once.
  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): This creates a special FULLTEXT index that is used for text-searching purposes.

Here is the example to add index in an existing table.

You can drop any INDEX by using DROP clause along with ALTER command. Try out the following example to drop above-created index.

You can drop any INDEX by using DROP clause along with ALTER command. Try out the following example to drop above-created index.

ALTER Command to add and drop PRIMARY KEY:

You can add primary key as well in the same way. But make sure Primary Key works on columns, which are NOT NULL.

Here is the example to add primary key in an existing table. This will make a column NOT NULL first and then add it as a primary key.

You can use ALTER command to drop a primary key as follows:

To drop an index that is not a PRIMARY KEY, you must specify the index name.

Displaying INDEX Information:

You can use SHOW INDEX command to list out all the indexes associated with a table. (wraparound by \G):

Try out the following example:

Design Patterns in PHP

There are number of ways to structure your code and project for your applications. Use of design patterns is usually a good idea to follow common patterns because it will make your code easier to manage and easier for others to understand.

We have five deigns patterns these are:

  • Factory
  • Singleton
  • Strategy
  • Front Controller
  • Model-View Controller

Factory

Factory pattern is most commonly used design patterns. In this pattern, a class simply creates the object you want to use. Consider the following example of the factory pattern:

This code uses a factory to create the Vehicle object. There are two possible benefits to building your code this way; the first is that if you need to change, rename, or replace the Vehicle class later on you can do so and you will only have to modify the code in the factory, instead of every place in your project that uses the Automobile class. The second possible benefit is that if creating the object is a complicated job you can do all of the work in the factory, instead of repeating it every time you want to create a new instance.

Using the factory pattern isn’t always necessary (or wise). The example code used here is so simple that a factory would simply be adding unneeded complexity. However if you are making a fairly large or complex project you may save yourself a lot of trouble down the road by using factories.

Singleton

When designing web applications, it often makes sense conceptually and architecturally to allow access to one and only one instance of a particular class. The singleton pattern enables us to do this.

The code above implements the singleton pattern using a static variable and the static creation method getInstance(). Note the following:

  • The constructor __construct() is declared as protected to prevent creating a new instance outside of the class via the new operator.
  • The magic method __clone() is declared as private to prevent cloning of an instance of the class via the clone operator.
  • The magic method __wakeup() is declared as private to prevent unserializing of an instance of the class via the global function unserialize() .
  • A new instance is created via late static binding in the static creation method getInstance() with the keyword static. This allows the subclassing of the class Singleton in the example.

The singleton pattern is useful when we need to make sure we only have a single instance of a class for the entire request lifecycle in a web application. This typically occurs when we have global objects (such as a Configuration class) or a shared resource (such as an event queue).

You should be wary when using the singleton pattern, as by its very nature it introduces global state into your application, reducing testability. In most cases, dependency injection can (and should) be used in place of a singleton class. Using dependency injection means that we do not introduce unnecessary coupling into the design of our application, as the object using the shared or global resource requires no knowledge of a concretely defined class.

 

Strategy

With the strategy pattern you encapsulate specific families of algorithms allowing the client class responsible for instantiating a particular algorithm to have no knowledge of the actual implementation. There are several variations on the strategy pattern, the simplest of which is outlined below:

This first code snippet outlines a family of algorithms; you may want a serialized array, some JSON or maybe just an array of data:

By encapsulating the above algorithms you are making it nice and clear in your code that other developers can easily add new output types without affecting the client code.

You will see how each concrete ‘output’ class implements an OutputInterface – this serves two purposes, primarily it provides a simple contract which must be obeyed by any new concrete implementations. Secondly by implementing a common interface you will see in the next section that you can now utilise Type Hinting to ensure that the client which is utilising these behaviours is of the correct type in this case ‘OutputInterface’.

The next snippet of code outlines how a calling client class might use one of these algorithms and even better set the behaviour required at runtime:

The calling client class above has a private property which must be set at runtime and be of type ‘OutputInterface’ once this property is set a call to loadOutput() will call the load() method in the concrete class of the output type that has been set.

 

Front Controller

The front controller pattern is where you have a single entrance point for your web application (e.g. index.php) that handles all of the requests. This code is responsible for loading all of the dependencies, processing the request and sending the response to the browser. The front controller pattern can be beneficial because it encourages modular code and gives you a central place to hook in code that should be run for every request (such as input sanitization).

Model-View-Controller

The model-view-controller (MVC) pattern break up code into logical objects that serve very specific purposes. Models serve as a data access layer where data is fetched and returned in formats usable throughout your application. Controllers handle the request, process the data returned from models and load views to send in the response, and views are display templates (markup, xml, etc) that are sent in the response to the web browser.

MVC is the most common architectural pattern used in the popular frameworks.

Helpful Links:

kfc.i.illuminationes.com Malware attack on WordPress

How this malware affect to your website?
This malware added a infected JS to your website pages and because of that whenever you open page it redirects to somewhere else. If antivirus installed on your machine then it will raise notice that website is harmful to your website.
How to prevent?
First reason for this attack is your password. If your password is weak then it will affect to your website very easily.
First way to avoid this type of malware attack, update your password to strong one so that your password never gets hack.
If your website is already affected with this malware then you need to follow below mentioned steps to remove it from your website.
1. You need to find out the affected files so that you can remove this malware from your website, to find out affected files –
1 Disable all your plugin and themes, and reload your website then checkout that affected JS are are loaded or not. If malware not loaded with your page means that any one of your themes or plugins is affected with this malware.
Now the simple way to remove this affected plugins and themes is manually activate one by one plugin and reload webpage and checkout that malware is loaded or not, so that you can easily find out the affected themes and plugins.
2. Another simple way is – this malware update your file type to some suspected file type. So one way is manually find out suspected file types and remove that suspected files.
3. Third way to remove this malware is simply download all your code on your system and scan your root folder with help of good antivirus, it will easily remove infected files from your code base.
4. Sometime this malware affect your WordPress files so in that case you need update your WordPress to latest version.