indexes

[mystrymaster]

Would some of you database mavens out there explain to me a couple of things.

What exactly are indexes?

What is the correct usage of them, and how do you determine where and when you need them?

Are there any drawback to using them?

I know these are questions that I could get answers from a mysql manual however I feel that the answers from this forum are better then techinical data

[khaldryck]

the Mysql manuals is quite very clear about it i think, but here is a short explanation :

Indexes are used to make big queries return the result faster.

if you often search a table using id or using date_creation,
it woudl be good to put primary index on id, and antoher index on date_creation.

when you query that table, it will use the index to find the result, instead of browsing all the table.

the drawback is that the more indexes you have the slower the insert and updates on that table.

check what indexes you need based on the query you execute the more often, then and do some benchmark to see if you adding that index really increase your performance.

also when executing a select with join, indexes can be used to increase by far the speed.

use explain to see if your indexes are well made.

[superwormy]

Everythign that he said, as a real world example, I have a query that does this:

SELECT ID, Prefix, domain, datetime FROM tablename WHERE category_ID = 15

The table has 98,000 records, WITHOUT an INDEX on catgory_ID, the query takes 1.75 seconds. WITH the INDEX on category_ID, 0.16 seconds. Quite a difference there :-)

Put INDEXes on fields you use in the WHERE clause often.