Indexing mysql tables
There is a way how to optimize mysql query execution time. And that is called index. Indexes help to find rows in tables faster, but it also increase insertion and update time. So it is best to use indexes in tables with a lot of rows and which are usually selected and more rarely updated or new rows inserted.
To insert index you need to execute this query (modified with your table information):
CREATE INDEX index_name ON table_name(column_name1, column_name2)
It is possible to insert multiple column index like in example above.
So how to determine what column should be indexed? Well you must look at queries you execute. Look what is in your searching term (what goes after WHERE word).
So if you have query:
SELECT * FROM tablename WHERE column_name1 = '1' and column_name2='1'
then you can use indexing as in example above. It will also optimize this query:
SELECT * FROM tablename WHERE column_name1 = '1'
but not this one with column_name2:
SELECT * FROM tablename WHERE column_name2='1'
To optimize query with column_name2, you will need to add another index where column_name2 is the first (or only one) to be indexed like this:
CREATE INDEX index_name ON table_name(column_name2)
You may also be interested in:
Powered by BlogAlike.com