How do you properly use an index???
Moderator: General Moderators
- voodoo9055
- Forum Commoner
- Posts: 51
- Joined: Sat Apr 26, 2003 3:27 pm
- Location: Montgomery, AL
How do you properly use an index???
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?
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.
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.
- voodoo9055
- Forum Commoner
- Posts: 51
- Joined: Sat Apr 26, 2003 3:27 pm
- Location: Montgomery, AL
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
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
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
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.pdffractalvibes wrote:I am fond of using autoincrement columns for IDs that uniquely identify. Those are always primary key indices for me.
-
Cruzado_Mainfrm
- Forum Contributor
- Posts: 346
- Joined: Sun Jun 15, 2003 11:22 pm
- Location: Miami, FL
example:
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:
or
let's say i want the name of all the users that are from the id 3 to 7
Code: Select all
їuserid]їusername ]їemail ]
---------------------------------------
0 recyclebin mymail@mail.com
1 someguy hismail@wut.com
2 somegurl hermail@there.netlet'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'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
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
.... where username like 'some%' ...
I don't think the DB can use the index on username in such an expression.
fv