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: