What is an index in SQL

0 votes
Also, when is it appropriate to use one?
Feb 3 in Database by Neha
• 6,260 points
29 views

1 answer to this question.

0 votes

An index is used to speed up searching in the database. MySQL have some good documentation on the subject (which is relevant for other SQL servers as well): http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

An index can be used to efficiently find all rows matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server has to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

In MySQL you can use EXPLAIN in front of your SELECT statement to see if your query will make use of any index. This is a good start for troubleshooting performance problems. Read more here: http://dev.mysql.com/doc/refman/5.0/en/explain.html

answered Feb 3 by Vaani
• 5,040 points

Related Questions In Database

0 votes
1 answer

What is the SQL query to get the third highest salary of an employee from employee_table

You can try out something like this: SELECT ...READ MORE

answered Sep 27, 2018 in Database by Sahiti
• 6,360 points
7,047 views
0 votes
1 answer

What is subquery in SQL?

A subquery is a query inside another ...READ MORE

answered Oct 12, 2018 in Database by DataKing99
• 8,240 points
827 views
0 votes
1 answer

What is the definition of cardinality in SQL?

Cardinality is defined as the "number of ...READ MORE

answered Feb 15 in Database by Neha
• 6,260 points
83 views
0 votes
0 answers

What is the definition of cardinality in SQL?

My book definition of Database Systems does ...READ MORE

Feb 18 in Database by Neha
• 6,260 points
71 views
0 votes
1 answer
0 votes
1 answer

How to change the default value and to alter a column in sql?

Hi, You can try this: ALTER TABLE foobar_data CHANGE ...READ MORE

answered Jun 24, 2019 in Big Data Hadoop by Gitika
• 65,850 points
281 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function: SELECT name, description, ... FROM ... WHERE id ...READ MORE

answered Feb 4 in Database by Neha
• 6,260 points
198 views
0 votes
1 answer
0 votes
1 answer

What is the difference between HAVING and WHERE in SQL?

HAVING: It is used to check after the aggregation ...READ MORE

answered Feb 17 in Database by Vaani
• 5,040 points
31 views
0 votes
1 answer

Number of rows affected by an UPDATE in PL/SQL

You can try using the following query: SELECT ...READ MORE

answered Feb 14 in Database by Vaani
• 5,040 points
52 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP