What exactly is Indexing?

[chantown]

Hi, I have read that indexing will make your queries faster, but it requires more hard drive space.

 

What exactly is it , and how should I do it for my situation?

 

I am building a forum much like SMF.

Usually I am selecting many things, so much of my queries are SELECT * FROM ...where topic_id = ... stuff like that

 

Since I am selecting * all the time (all the columns I need to select), should I index EVERY column?

 

Do I index the stuff being DRAWN or the stuff being SEARCHED?

 

Like, how do I determine what to index or what not to index

 

many thanks!

[btherl]

It's not a simple question.  But in general, you should index at least one column from the "WHERE" conditions of your queries.

 

For example, if you are always searching "where topic_id = ...", then you will very likely get a more efficient system by creating an index on topic_id.

 

In nearly all cases, you should index the stuff SEARCHED.  But in some rare cases you can in fact benefit from indexing the stuff DRAWN.  I would not worry about that yet though.

 

To find if you need an index or not, try timing your queries.  If the query is looking slow, take a look at the "where" clause and consider what index would narrow down the possible values enough to make the query fast.

[chantown]

When I refresh a page to get the updated stuff on my forum (time of posts, usernames, topic titles), I find that when i use "top" in my Linux, the MYSQL Process uses 3.9% of CPU power. Is this normal? And will it decrease when I index?

 

(I have a pentium D 3.0 Dual core 4 GB ram)

[fenway]

Forgot about top... use mysql's internal performance indicators.

[chantown]

wats that?

[fenway]

Read the stickies on optimization.