Columns used in the WHERE clause is the good identifier for which columns to
index. You don't want to index every column you select.
Typically adding indexes decreases insert performance. The more indexes the
slower inserts operate, because the indexes also must be updated.
Adding an index to a column which is used in a where clause can dramatically
increase table row selection. The database server does not have to scan the
full table, instead it can use the index to lookup which rows to return.
On small static tables the performance gain can be nominal. Larger tables
where there are a lot of rows to check for a match, will see the largest
performance gain.
Think of a dictionary. You already know the index: words are alphabetically
sorted. Just think if it wasn't, you'd have to look at every word to see if
you've got a match. Without indexes, that's what the computer has to do.
The computer (dbms) picks which index to use based on whats column in the
where clause.
Order By clauses can also benifit from an index.
-Joe
""Steve Brett"" <[EMAIL PROTECTED]> wrote in message
99pjr2$20s$[EMAIL PROTECTED]">news:99pjr2$20s$[EMAIL PROTECTED]...
> replies below:
>
>
> ""M. Verheijen"" <[EMAIL PROTECTED]> wrote in message
> [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Dear reader,
> A newbie mysql/php-question here! I've filled a mysql-database with about
> 1600 records. All these records contain items which
> are on sale on a website. Every row contains an integer defining the
> category to which a item belongs.
>
> At the left of the website there are buttons linked to a php-page which
does
> a selection like this:
> select * from PRODUCTS where SUBCODE = \"$SUBCODE\"
>
> As you might have guessed, every button is a link in the form of:
> <a href="products.php?SUBCODE=8"><img src="../button2.gif" border="0"></a>
>
> This all works well, but now my questions!
> Is it wise to make an index for the integer column? I assume it is because
> it's the row almost all queries are using in there where-statements. What
> kind of speed increase can I expect, will it increase the speed of query's
> if the workload goes to 2000 queries a hour? What's the best way to make
> this index, how does it works.
>
> >>>>>>
> i always work on the theory that i index columns that i use in the select
> section of a query, or a where.
> keys will be indexed automatically (AFAIU).
> the speed increase will be dramatic. i used postgres and didn't index a
> field (accidently) and then ran queries.then indexed it and the speed
> increase is huge.
> as for making indexes it depends what tools you use. most tools such as
> mysql-admin etcc will let you create indexes by a pointy clicky interface.
> true purists might insist on:
>
> CREATE [UNIQUE] INDEX index_name ON table [USING acc_name]
>
> from the sql command line.
>
> <<<<<<<<<<<<<<<<<<<
>
> I've read about the use of indexes in the mysql-manual and phpbuilder.net,
> but both are kind of short and don't tell what
> you're doing exactly. Anybody of you have some pointers to good
webresources
> about indexing.
>
> >>>>>>>>>>>>>>>>>>>>
> i don't think you need to get into what particular type of index it is
> (hash, etc) but they are generally a good thing.
> <<<<<<<<<<<<<<<<<<<<<<
>
> Yes, I know a long list of questions ;) I hope that some of you can give
me
> a clue or two.
>
> >>>>>>>>>>>>>>>>>>>>>>>
> log lists of questions are a good thing and should be warmly welcomed by
> everyone.
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> With best regards,
> Maarten Verheijen
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
