How can I use indexes to optimize my message board?

I'm relatively new to databases, and have read a lot of tutorials
lately, but the more I learn, the more I realize that I haven't
learned anything yet! LOL

I've converted a message board from using plain text files to using
MySQL. Per some excellent suggestions on here, I have it set up as 2
tables:

subjects
posts

The "subjects" table acts as a cross-reference, holding the ID#,
subject name, and last modified date, and currently has 17000 rows of
data. The "posts" table holds all of the posts, which is about 600,000
rows of data.

My problem is SPEED. I would swear that the new MySQL format is slower
than the old text format! LOL I'm sure that there's some code that
can be optimized, but that's really more appropriate for a Perl ng.

Here's my question for you guys. If I understand the concept of an
index, it basically takes the first few characters of a table and adds
it to a separate table that can be searched more quickly because of
the lower amount of data, and then that table cross-references the
"real" table. Is that pretty much the gist of it? That's what I
gathered from the MySQL documentation, although it left me with my
head spinning.

If so, how can using an index would help me, when the search is by a
unique ID#? Is there another way to make it find the ID more quickly
than by sorting through 17000+ rows to find the one that matches?

TIA,

Jason

Jason wrote:
> If I understand the concept of an
> index, it basically takes the first few characters of a table and adds
> it to a separate table that can be searched more quickly because of
> the lower amount of data
Nope that's not it at all. This section in the manual explains it:
http://dev.mysql.com/doc/refman/5.0/...l-indexes.html
Basically a database uses an index in the same wat that if you were looking
for something in a book, you would use ........ an index!

Instead of having to read through the whole book to find a specific word,
you can look at the index and it'll tell you immediately what page to go to.

So, think about where your application needs to find something in the
database and make sure that it can use an index on it.

Now it isn't quite as simple as that, but that is where experience and the
correct analytical mindset comes in.

Remember that any primary key will automatically be an index.

In order to be able to help you further, we'll need to see your actual table
schemas and the queries that you are using.

== Quote from Jason (jwcarlton@gmail.com)'s article
> I'm relatively new to databases, and have read a lot of tutorials
> lately, but the more I learn, the more I realize that I haven't
> learned anything yet! LOL
> I've converted a message board from using plain text files to using
> MySQL. Per some excellent suggestions on here, I have it set up as 2
> tables:
> subjects
> posts
> The "subjects" table acts as a cross-reference, holding the ID#,
> subject name, and last modified date, and currently has 17000 rows of
> data. The "posts" table holds all of the posts, which is about 600,000
> rows of data.
> My problem is SPEED. I would swear that the new MySQL format is slower
> than the old text format! LOL I'm sure that there's some code that
> can be optimized, but that's really more appropriate for a Perl ng.
> Here's my question for you guys. If I understand the concept of an
> index, it basically takes the first few characters of a table and adds
> it to a separate table that can be searched more quickly because of
> the lower amount of data, and then that table cross-references the
> "real" table. Is that pretty much the gist of it? That's what I
> gathered from the MySQL documentation, although it left me with my
> head spinning.
> If so, how can using an index would help me, when the search is by a
> unique ID#? Is there another way to make it find the ID more quickly
> than by sorting through 17000+ rows to find the one that matches?
> TIA,
> Jason
an index makes searches a lot faster. if a record is in the bottom of your table,
an indexed search will find the record by reading one row whereas an unindexed
search will have to scan the whole table to find the record.
--
POST BY: lark with PHP News Reader

> an index makes searches a lot faster. if a record is in the bottom of your table,
> an indexed search will find the record by reading one row whereas an unindexed
> search will have to scan the whole table to find the record.

Well, I thought I understood...

Here's why I think I'm getting lost. Is the index something that I can
PHYSICALLY use, or is it automatically used when I access a table?

For instance, let's say that I have a table with 100,000 rows, and the
columns are (id, username, password, status, description), where
(description) is 500 characters. But in order to retrieve data, I
simply need to find the matching (id).

Here's the code that I'm currently using like this (written in Perl):
my $topiclist = $dbh->selectall_arrayref("SELECT id, username,
password, status, description FROM users WHERE id=" . $dbh-
>quote(param('id')) . " ORDER BY postdate DESC LIMIT 20");

If I set (id) as the PRIMARY KEY, do I need to change the program in
order to make it access the index and run more quickly, or is that
just automatically done when I search the table?

TIA,

Jason

>Here's why I think I'm getting lost. Is the index something that I can
>PHYSICALLY use, or is it automatically used when I access a table?

It is possible in MySQL to specify an index, but usually it's better
to let MySQL decide which one to use, if any. (Examples: "SELECT
* FROM table" probably won't use any index (you get the whole thing
anyway). "SELECT * FROM table where id = 5 and count = 3" might
use an index on (id, count), (count, id), id, or count. You wouldn't
have all 4 of those indexes available, but you might have more than
one.)


>For instance, let's say that I have a table with 100,000 rows, and the
>columns are (id, username, password, status, description), where
>(description) is 500 characters. But in order to retrieve data, I
>simply need to find the matching (id).
>
>Here's the code that I'm currently using like this (written in Perl):
>my $topiclist = $dbh->selectall_arrayref("SELECT id, username,
>password, status, description FROM users WHERE id=" . $dbh-
>>quote(param('id')) . " ORDER BY postdate DESC LIMIT 20");
>
>If I set (id) as the PRIMARY KEY, do I need to change the program in
>order to make it access the index and run more quickly, or is that
>just automatically done when I search the table?

No change needed. However, making some field a primary key or
unique index prohibits you from having rows with duplicate values
for that field. This might be intended anyway, or it might be a
problem. You can have non-unique indexes if you want.

On 2 Aug, 04:04, Jason <jwcarl...@gmail.com> wrote:
> > an index makes searches a lot faster. if a record is in the bottom of your table,
> > an indexed search will find the record by reading one row whereas an unindexed
> > search will have to scan the whole table to find the record.
>
> Well, I thought I understood...
>
> Here's why I think I'm getting lost. Is the index something that I can
> PHYSICALLY use, or is it automatically used when I access a table?
Did you read the Manual starting at the link that I posted.

> > Well, I thought I understood...
>
> > Here's why I think I'm getting lost. Is the index something that I can
> > PHYSICALLY use, or is it automatically used when I access a table?
>
> Did you read the Manual starting at the link that I posted.

I had actually read it before I posted (the tutorial was my starting
point about 2 weeks ago), and read it again after you posted it. I
know the information is there, I just couldn't quite wrap my head
around it.

I'm sure that it makes perfect sense once you know what you're doing,
but being almost completely new to DBI altogether, it just didn't
click.

- J

> It is possible in MySQL to specify an index, but usually it's better
> to let MySQL decide which one to use, if any. (Examples: "SELECT
> * FROM table" probably won't use any index (you get the whole thing
> anyway). "SELECT * FROM table where id = 5 and count = 3" might
> use an index on (id, count), (count, id), id, or count. You wouldn't
> have all 4 of those indexes available, but you might have more than
> one.)

That makes sense, thanks. Once I stopped thinking of the index as a
separate entity, it's coming together better.


> No change needed. However, making some field a primary key or
> unique index prohibits you from having rows with duplicate values
> for that field. This might be intended anyway, or it might be a
> problem. You can have non-unique indexes if you want.

In this case, that is intended.

You guys have been a lot of help, and I really appreciate it. Thanks
again,

Jason