Indexing / when & what to index?

[Tad]

I've been writing a search script, and I noticed that it's pretty slow since it has to go through all sorts of data before spitting back results.

So... I started searching on here and now I've been trying to learn about indexing because I think that's what I need to do. But of course, I've got questions. What fields are you supposed to index? The ones that are in your where clause? When should you index? I also found out that too much indexing = bad because it can slow up inserting/updating data and whatnot.

Anyway, I'd really appreciate it if someone could point me in the right direction. Thanks. 🙂

[bubblenut]

Indexes slow inserts and updates because the DBMS has to insert/update the indexes as well. They also take up more space on the disk, however they do make searching quicker. As a general rule I say don't use indexes on text fields, by this I don't just mean MySQL's BLOBs or things like that (definately don't try to index those!) but also varchars larger than a few characters. Yes, as you said, only index fields which appear in where clauses. There's no point in indexing something which the DBMS never has to search on. This doesn't mean index every field which appears in any where clause. If there's a field which appears in one, rarely used where clause then it's probably not worth the cost of having an index on it; on the other had it may be a mission critaical query which absolutely needs to be processed quickly.

All I can give you is a little insight, eventually, in the tenable words of Bruce Forsyth, "The choice is yours"

HTH
Bubble

[phil_j_poore]

you can do very complicated searches on strings quite quickly i am not sure how faster and i am not sure how much space it takes up but MySQL has a FULLTEXT index which allows for all kinds of searching stings.
Find out more ------>FULL TEXT SEARCHING
hope this helps

[the_php_guy]

try to index the fields that appear in your WHERE statement
that helps a lot.
for example ... WHERE status = 3

then index the status field.

[Roger_Ramjet]

Basic principle:
Index fields used to join tables, whether they are a Primary Key - Foriegn Key pair or not.

Index fields frequently used in Where clauses - IF there is a lot of variety in the contents of that field (it would be a waste of time to index a Gender field for instance, M/F).

If you want to index a character field then make sure that it is char not varchar.

If the table contains a text field that will result in mysql doing a Silent type conversion them move that field to a seperate table with the Primary key and use a join when you want it, allowing you to preserve char fields in the main table.

Integer indexes work faster than other data types, so use autoinc fields for Primary Keys not the data itself.

Finally, use EXPLAIN with your main queries to see if your indexing can be improved.

[Tad]

Okay, I think I'm getting the idea now. 🙂 I'm actually running the searches on some tables that have member data in them. Like, I have profileid, name, age, gender, whether the profile is pending or not, and there's also an interest field that members can sort others by, that sort of thing.

The age, interests, and pending field all rely on searching short numerical values. I'm thinking that the interest field should definately be indexed, since it's going to used quite a bit, and since it also relies on another table to match the interestid with the interest name.

It makes sense that fields that are to be joined to fields on another table should be indexed.

Hm, I guess another thing I'm wondering about is the name field. I've got it so that users can search for other users with "WHERE name LIKE '$name%'". At the moment I've got the field set as a varchar(50). So I'm guessing that I should probably index that one as well.

I'm still reading up on this all, ahha. There's just so much to learn and absorb. Thanks for your replies so far.