Index problem with table

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…

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’

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);

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!

Thank you… I will look at that

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.

Sponsor our Newsletter | Privacy Policy | Terms of Service