MYSQL Indexing Query Explained Clearly

Posted by in Articles, SQL

Proper indexing and good method to fetch only necessary data is one of the crucial parts for a successful and safe database. When proper syntax is given to the database, it will fetch near to perfect data which also reduces the load time and saves your server bandwidth. However it might be negligible in case of small data, it is recommend getting only the things you need. Today you’ll be cleared about the MySQL query commands that you most of the time send from PHP page.

Remember, in this tutorial we’ll be learning only the database indexing part. Database entry part will be discussed later in upcoming tutorials.

First let’s assume that we have a basic table named user_info with username, gender, age, password, etc. For example, see the image below we’ve a complete table (user_info table) with following information.

Indexing all the contents from the table

To index all the contents from the table name user_info, we must use following syntax.

SELECT * TABLE_NAME

For table user_info, you can give following syntax:

SELECT * user_info

This syntax will whole contents from table user_info

Indexing only one column from a table

You may want to list only the username from the user_info table. This means that you are indexing only one column. The syntax you’ll use is:

SELECT username FROM user_info

Indexing more than one column from a table

There are more than one thing you may want to index from the database. For example let me show you how you can index more than one column from a table. Use following syntax.

SELECT userID, username FROM user_info

This will list two columns, userID and username from the table user_info.

If you want to list more tables, separate them by commas.

SELECT column1, column2, column3, columnX FROM user_info

Indexing a single row from the table

Indexing a single row is necessary when you want to get the full information about the username. With row indexing you can access all the information about the username. The syntax to index a horizontal row is:

SELECT * FROM user_info WHERE username = ‘sita’

This will index the row with username sita.

Note that we’ve added WHERE syntax and queried sita from username column.

Like username information, you can take other references which are unique in the column. For example, userID.

SELECT * FROM user_info WHERE userID = ‘4’

Indexing multiple rows with similar information

This method can be used when you want to list multiple rows of information with same information. For example, Gender.

SELECT * FROM user_info where Gender  = ‘Male’

This will list all the rows with Gender = Male

Indexing multiple rows with other operands

Similarly, with other operands you can index more columns. For example, age group.

SELECT * FROM user_info WHERE Age < ’25’

It will index all the rows that has age lesser than 25.

These are only the basic and illustrated SQL syntaxes. Hope this made you clear about the SQL indexing. Mix up all of the techniques above to fetch a proper and enough data necessary to you.

Guest post by DigitalBunch.com