how to use MySQL INDEXES?

asked Sep 13, 2013 in MYSQL by ashish singh
edited Sep 12, 2013
0 votes

3 Answers

0 votes
This comes up in discussions almost every new project I work on, because it's a very important thing to consider when designing a database. When deciding when and how to create an index in your MySQL database, it's important to consider how the data is being used. Let's say you have a database of employees. We will create it like this: CREATE TABLE employees ( ID INT, name VARCHAR(60), salary decimal(10,2), date hired(date) ) So you will notice that this table is pretty simplistic, and doesn't really contain all the info you would need to actually manage employees, but its just for the sake of demonstration, and you could always add more later, or even make another table and use joins if you had really complex needs. For now we will go over these real quick. The ID is basically just a number (INT) which can hold a very large number. If this were real world I would probably make it unsigned, since you will never have a negative employee ID – but either way, you will never reach the number of employees it would take to get to the number that would fill up an INT. Even unsigned int will hold values up to 2,147,483,647. So if you have 2 billion employees, you would probably not be a developer anymore ;-). You might want to consider making the field an auto increment, and primary key, the auto increment depending on how data will be entered into this database. Name is a simple varchar(60) which should cover most people's names. Salary is a decimal with 10 total digits, two on the right hand side of the decimal point. This would handle a salary of up to 99,999,999.99 – again, you're not likely to hit this limit. Date hired will be a date in this format 2010-05-06. YYYY-MM-DD. So when considering this simple table, where would you expect to need an index? If we assign ID as a primary key, we don't need one there. Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by". You should also pay attention to whether or not this information will change frequently, because it will slow down your updates and inserts. Since you wont frequently be adding employees, you don't have to worry about the inserts. Let's say that you will be looking up the employees with a php web interface and the end user will be typing in the employees name to find them, since remembering the employee ID's would be cumbersome. It sounds like this situation would be good to use an index. A – You won't be updating the employee's name very often, so you don't have to worry about a performance hit there. B – You WILL be using the employee in where clauses like this: select * from employees where name ='smith'; C – You WILL be generating reports, which will probably be alphabetic, like this: select * from employees order by name asc; So in this simple example it's easy to see when it would be important to use indexes. So, you could do it like this: create index name_index on employees (name); You might be working on a more complex database, so it's good to remember a few simple rules. - Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated. - Indexes speed up where clauses and order by. Remember to think about HOW your data is going to be used when building your tables. There are a few other things to remember. If your table is very small, i.e., only a few employees, it's worse to use an index than to leave it out and just let it do a table scan. Indexes really only come in handy with tables that have a lot of rows. So, if Joe’s Pet Shop was using this database, they would probably be able to leave the index off the "name" column. If Microsoft was using this database (hah!) they might want to throw and index in there. Another thing to remember, that is a con in the situation of our employees database, is that if the column is a variable length, indexes (as well as most of MySQL) perform much less efficiently. As you can see there are many things to consider with indexes, even with a very simple table as this. I would suggest looking at the explain command in MySQL, which I will be writing about in the future.
answered Sep 13, 2013 by ashish singh
edited Sep 12, 2013
0 votes
A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
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 keeps primary key or index field and a pointer to each record in to 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 takes 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 inert 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
ON table_name ( column1, column2,...);
You can use one or more columns to create an index. For example we can create an index on tutorials_tbl using tutorial_author
ON tutorials_tbl (tutorial_author)
You can creates 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:
ON tutorials_tbl (tutorial_author DESC)
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.
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
You can drop any INDEX by using DROP clause along with ALTER command. Try out following example to drop above created index.
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
You can drop any INDEX by using DROP clause along with ALTER command. Try out following example to drop above created inde x.
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.
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
You can use ALTER command to drop a primary key as follows:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
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. Vertical-format output (specified by \G) often is useful with this statement, to avoid long line wraparound:
Try out following example:
mysql> SHOW INDEX FROM table_name\G
answered Sep 13, 2013 by ashish singh
edited Sep 12, 2013
0 votes
ndexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially.
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.
In general, indexes are used as described in the following discussion. Characteristics specific to hash indexes (as used in MEMORY tables) are described at the end of this section.
MySQL uses indexes for these operations:
To find the rows matching a WHERE clause quickly.
To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).
To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.
Comparison of dissimilar columns may prevent use of indexes if values cannot be compared directly without conversion. Suppose that a numeric column is compared to a string column. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. This rules out use of any indexes for the string column.
To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example:
SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;
To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order. See Section 8.13.9, “ORDER BY Optimization”, and Section 8.13.10, “GROUP BY Optimization”.
In some cases, a query can be optimized to retrieve values without consulting the data rows. (An index that provides all the necessary results for a query is called a covering index.) If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values can be retrieved from the index tree for greater speed:
SELECT key_part3 FROM tbl_name
  WHERE key_part1=1
Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query. See Section, “How to Avoid Full Table Scans” for details.
answered Sep 13, 2013 by ashish singh
edited Sep 12, 2013