How do you properly use an index???

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
voodoo9055
Forum Commoner
Posts: 51
Joined: Sat Apr 26, 2003 3:27 pm
Location: Montgomery, AL

How do you properly use an index???

Post by voodoo9055 »

I read up on indexes, but I am still having a hard time grasping the concept. I have an application that searches fine right now. But I realize as the database gets bigger, performance will get smaller. I understand how to create indexes, but I am not sure when it is appropriate to use them. Do you use them on fields you expect to search for a lot or do you use them on filtering fields after the WHERE clause? Also is there something special in the sql statement you will have to do to call an index?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

A short answer would be to always index cols used in WHERE clauses, ORDER BY's, and the USING() clause of a JOIN query (ie the cols used to join two tables).

You don't have to do anything special to make the database use an index - although you can specify which indexes to use or ignore in a JOIN query.

If you're using mysql, make sure you get a copy of the manual from mysql.com - all the gory details in there.
User avatar
voodoo9055
Forum Commoner
Posts: 51
Joined: Sat Apr 26, 2003 3:27 pm
Location: Montgomery, AL

Post by voodoo9055 »

Thank you for the heads up.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Good answer McGruff. I am fond of using autoincrement columns for IDs that uniquely identify. Those are always primary key indices for me. You really have to look at how the data is most used in your sql.
Indices are great, but there is a cost associated - they also have to be updated when you do adds/deletes /and possibly updates. Judicious use can enhance performance a good deal!

fv
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

fractalvibes wrote:I am fond of using autoincrement columns for IDs that uniquely identify. Those are always primary key indices for me.
Sheesh - I forgot to mention that. Most tables should of course have an auto-increment, primary key. If you're not sure why these are required check this out: http://www.oreilly.de/catalog/javadtabp ... /ch02.pdf
Cruzado_Mainfrm
Forum Contributor
Posts: 346
Joined: Sun Jun 15, 2003 11:22 pm
Location: Miami, FL

Post by Cruzado_Mainfrm »

example:

Code: Select all

їuserid]їusername  ]їemail            ]
---------------------------------------
0        recyclebin  mymail@mail.com
1        someguy     hismail@wut.com
2        somegurl    hermail@there.net
where ID is the INDEX
let's say i'm logged in as someguy, and i want to preview my email, i just go like:

Code: Select all

SELECT email FROM table WHERE userid = '1'
or
let's say i want the name of all the users that are from the id 3 to 7

Code: Select all

SELECT username WHERE userid >= '3' AND userid <= '7'
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

You could also define indices on username and/or email if appropriate - depends upon how you might search on them. If the search argument will always be the full text such as recyclebin or mymail@mail.com , indices could help. If you are using terms such as:

.... where username like 'some%' ...
I don't think the DB can use the index on username in such an expression.

fv
Post Reply