# Index problem with table

**URL:** <https://www.phphelp.com/t/index-problem-with-table/33064>\
**Category:** MySQL database\
**Created:** [January 25, 2021, 6:33pm UTC](https://www.phphelp.com/t/index-problem-with-table/33064 "2021-01-25T18:33:42Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mariusd](https://www.phphelp.com/letter_avatar_proxy/v4/letter/m/ac91a4/32.png) [@mariusd](https://www.phphelp.com/u/mariusd)\
**Post date:** [January 25, 2021, 6:33pm UTC](https://www.phphelp.com/t/index-problem-with-table/33064/1 "2021-01-25T18:33:42Z")

</div>

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

![image](https://www.phphelp.com/uploads/default/original/2X/4/4d668e25212efd477136bb692914b9f6af9de8ef.png)

---

<div class="post-metadata">

**Author:** ![ErnieAlex](https://www.phphelp.com/letter_avatar_proxy/v4/letter/e/bc8723/32.png) [@ErnieAlex](https://www.phphelp.com/u/ErnieAlex)\
**Post date:** [January 25, 2021, 7:42pm UTC](https://www.phphelp.com/t/index-problem-with-table/33064/2 "2021-01-25T19:42:05Z")

</div>

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](http://mysql.rjweb.org/doc.php/index_cookbook_mysql) Good luck!

---

<div class="post-metadata">

**Author:** ![mariusd](https://www.phphelp.com/letter_avatar_proxy/v4/letter/m/ac91a4/32.png) [@mariusd](https://www.phphelp.com/u/mariusd)\
**Post date:** [January 25, 2021, 8:19pm UTC](https://www.phphelp.com/t/index-problem-with-table/33064/3 "2021-01-25T20:19:07Z")

</div>

Thank you… I will look at that

---

<div class="post-metadata">

**Author:** ![skawid](https://www.phphelp.com/letter_avatar_proxy/v4/letter/s/bbe5ce/32.png) [@skawid](https://www.phphelp.com/u/skawid)\
**Post date:** [January 26, 2021, 8:08am UTC](https://www.phphelp.com/t/index-problem-with-table/33064/4 "2021-01-26T08:08:30Z")

</div>

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.