Proper Indexing of Tables in MySQL
In general you should only add indexes to match the queries your application uses, any extra will waste resources. In an application with very small tables, indexes will not make much difference but as soon as your tables are larger than your buffer sizes the indexes will start to speed things up dramatically.
When to add an index?
1. You have done an EXPLAIN and no index was shown as applicable.
NOTE: this may be MySQL deciding to do a table scan instead so an index will not help.
2. You want to search for a word in a field often using LIKE '%word%'.
Then add a fulltext index and use that, otherwise you are using a table scan which is very slow.
3. If your table has a large read and write qty consider using delayed writes, this uses the db engine in a "batch" write, which cuts down on disk io, therefore increasing performance.
See http://dev.mysql.com/doc/mysql/en/insert-delayed.html
INSERT values(a,b,c) into tablename (
4. If you are building a large table then for best performance add the index after the table is populated with data.
NOTE: this is to increase the insert performance and remove the index overhead during inserts.
When to remove an index?
1. If an index is rarely or not used then remove the index to increase INSERT, UPDATE,
When to add an index?
1. You have done an EXPLAIN and no index was shown as applicable.
NOTE: this may be MySQL deciding to do a table scan instead so an index will not help.
2. You want to search for a word in a field often using LIKE '%word%'.
Then add a fulltext index and use that, otherwise you are using a table scan which is very slow.
3. If your table has a large read and write qty consider using delayed writes, this uses the db engine in a "batch" write, which cuts down on disk io, therefore increasing performance.
See http://dev.mysql.com/doc/mysql/en/insert-delayed.html
INSERT values(a,b,c) into tablename (
4. If you are building a large table then for best performance add the index after the table is populated with data.
NOTE: this is to increase the insert performance and remove the index overhead during inserts.
When to remove an index?
1. If an index is rarely or not used then remove the index to increase INSERT, UPDATE,
Comments