Laravel Application with Admin LTE 2 implementation

Hello, Developers…

Many times we try to find out the best plugin for admin panel which we can use with Laravel. But nothing good is available. Even I was trying same but didn’t found any good solution so I decided to use Admin LTE 2 with Laravel and built the base app which we can use for any of our application as a base app and then later we can add our required module in it.

By using this base application you can do the following things:

  • Authentication.
  • Register new user.
  • User profiles.
  • Create different user types and users.
  • Assign roles to the user as per the user type.
  • Assign module permission.
  • Image upload.
  • Create responsive images in the backend for the user interface.

I am adding this application for the new developer so that they can learn Laravel as well as they can reffer the code base for their learning.

New developers have some specific issues at the initial level like:

How to create the new application in Laravel?

How to write effective code in Laravel and etc.

While creating this app I have taken care of all of this things.

If you need this application email me on my personal email. Will send you the download link.

 

Application Details:

Laravel – V 5.5

Apache –  2.4.27

PHP – 7.0

MySQL – 5.7.19

Here is the Download link.

 

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: