Page 1 of 1

How do you properly use an index???

Posted: Wed Oct 08, 2003 6:47 pm
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?

Posted: Wed Oct 08, 2003 7:04 pm
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.

Posted: Wed Oct 08, 2003 9:26 pm
by voodoo9055
Thank you for the heads up.

Posted: Thu Oct 09, 2003 10:10 pm
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

Posted: Fri Oct 10, 2003 12:40 am
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

Posted: Fri Oct 10, 2003 4:20 pm
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'

Posted: Fri Oct 10, 2003 10:46 pm
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