Index problem with table

[mariusd]

Can someone help me on finding the proper indexes for a table? using explain it keeps showing me that it uses where and filesort and it keep loading slowly and slowly when the rows amount keep pile up…

CREATE TABLE IF NOT EXISTS shoutbox (
id bigint(20) NOT NULL,
userid bigint(20) NOT NULL DEFAULT ‘0’,
to_user int(11) NOT NULL DEFAULT ‘0’,
username varchar(25) CHARACTER SET utf8 DEFAULT NULL,
date int(11) NOT NULL DEFAULT ‘0’,
text text CHARACTER SET utf8,
text_parsed text CHARACTER SET utf8,
staff_shout enum(‘yes’,‘no’) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘no’,
autoshout enum(‘yes’,‘no’) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘no’
) ENGINE=MyISAM AUTO_INCREMENT=272 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE shoutbox
ADD PRIMARY KEY (id), ADD KEY for (to_user), ADD KEY userid (userid), ADD KEY username (username), ADD KEY staff (staff_shout);

[ErnieAlex]

I am not sure what you are asking. I am sure it is a language issue.

But, I will guess. You create a table " shoutbox " and all looks normal there.
Then, you set up a large, very large PRIMARY KEY index. My guess is this is the problem.
You might want to test it with just the id field as a key. Just ADD KEY (id)…

Also, the primary key layout matters depending on how you retrieve data from the database. If you use a GROUP or ORDER BY option, sometimes you need to add other keys for faster searching. But, for general use, just index using the id field. Therefore, you would also need to show us your SELECT queries because the query defines how you set up the indexes.

If your English is good, you can read this site as it explains some of how to check your queries to see how you should index the fields. It might help. Index Cookbook Good luck!

[mariusd]

Thank you… I will look at that

[skawid]

You need to show us what queries you are running on the table as well; its these queries that dictate what indexes will be useful and what will slow you down.