StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POwhich indexes multiple field?
primarykey
Id
3666248
data
AcceptedAnswerId
0
AnswerCount
1
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2010-09-08T09:19:13.800
FavoriteCount
0
LastActivityDate
2010-09-08T09:30:11.770
LastEditDate
2010-09-08T09:24:01.173
LastEditorUserId
430997
OwnerUserId
430997
ParentId
0
PostTypeId
1
Score
0
ViewCount
49
LastEditorDisplayName
text
Body
I have a question for field indexing and I am not really sure how to do it. Which indexes should be multiple field index and which "stand alone"...sorry for my terminology :). I have a table where only insert,delete and select will be present, only a104 will be updatable, other fields will be "read only"... So I have 106 fields and data 105 fields will never change. Once the record is in the table it will stay unchanged...only after few days it will move to archive table. (Insert, delete, select with where will be present...). I will have simple and detail search page for searching the records. Insert frequency I suppose up to 2500 per day... Delete up to 1000 records per day...for now... Simple search will have select like this: SELECT * FROM my_table WHERE ad2='yyy' and ad4='qqq' (where part will always start with ad2 ! other field could be left...I suppose one index wiil be for this two fields: KEY <code>firstindex</code> (<code>ad2</code>,<code>ad4</code>) Advanced search will user few more fields in WHERE clause... a1,a2,a3,a4,a25,a26,a27,a28,a31,a32,a33,a35, and everything from a41 to a101 -->these all are bit fields All mentioned fields in simple and advanced search will contain data which will never change, only after while records will be deleted (moved to archive table...) So my question is how to set up indexes in my case to get best performance for select and compromise for insert and delete... This is my MyISAM table....(not really small one) <hr> CREATE TABLE <code>my_table</code> ( <code>id</code> int(11) unsigned NOT NULL auto_increment, <code>a1</code> varchar(100) NOT NULL default '', <code>a2</code> varchar(100) NOT NULL default '', <code>a3</code> varchar(100) NOT NULL default '', <code>a4</code> varchar(100) NOT NULL default '', <code>a5</code> varchar(60) NOT NULL default '', <code>a6</code> varchar(60) NOT NULL default '', <code>a7</code> varchar(15) NOT NULL default '', <code>a8</code> varchar(100) NOT NULL default '', <code>a9</code> varchar(60) NOT NULL default '', <code>a10</code> varchar(20) NOT NULL default '', <code>a11</code> varchar(60) NOT NULL default '', <code>a12</code> varchar(60) NOT NULL default '', <code>a13</code> varchar(60) NOT NULL default '', <code>a14</code> varchar(100) NOT NULL default '', <code>a15</code> varchar(60) NOT NULL default '', <code>a16</code> varchar(60) NOT NULL default '', <code>a17</code> varchar(15) NOT NULL default '', <code>a18</code> varchar(100) NOT NULL default '', <code>a19</code> varchar(60) NOT NULL default '', <code>a20</code> varchar(11) NOT NULL default '', <code>a21</code> varchar(60) NOT NULL default '', <code>a22</code> varchar(60) NOT NULL default '', <code>a23</code> varchar(60) NOT NULL default '', <code>a24</code> varchar(60) NOT NULL default '', <code>a25</code> date NOT NULL, <code>a26</code> time NOT NULL, <code>a27</code> date NOT NULL, <code>a28</code> time NOT NULL, <code>a29</code> decimal(2,0) NOT NULL default '0', <code>a30</code> decimal(25,6) NOT NULL default '0.000000', <code>a31</code> bit(1) NOT NULL, <code>a32</code> decimal(2,0) NOT NULL default '0', <code>a33</code> decimal(52,6) NOT NULL default '0.000000', <code>a34</code> varchar(50) NOT NULL default '', <code>a35</code> varchar(50) NOT NULL default '', <code>a36</code> varchar(50) NOT NULL default '', <code>a37</code> varchar(50) NOT NULL default '', <code>a38</code> varchar(50) NOT NULL default '', <code>a39</code> varchar(50) NOT NULL default '', <code>a40</code> varchar(50) NOT NULL default '', <code>a41</code> bit(1) NOT NULL default '\0', <code>a42</code> bit(1) NOT NULL default '\0', <code>a43</code> bit(1) NOT NULL default '\0', <code>a44</code> bit(1) NOT NULL default '\0', <code>a45</code> bit(1) NOT NULL default '\0', <code>a46</code> bit(1) NOT NULL default '\0', <code>a47</code> bit(1) NOT NULL default '\0', <code>a48</code> bit(1) NOT NULL default '\0', <code>a49</code> bit(1) NOT NULL default '\0', <code>a50</code> bit(1) NOT NULL default '\0', <code>a51</code> bit(1) NOT NULL default '\0', <code>a52</code> bit(1) NOT NULL default '\0', <code>a53</code> bit(1) NOT NULL default '\0', <code>a54</code> bit(1) NOT NULL default '\0', <code>a55</code> bit(1) NOT NULL default '\0', <code>a56</code> bit(1) NOT NULL default '\0', <code>a57</code> bit(1) NOT NULL default '\0', <code>a58</code> bit(1) NOT NULL default '\0', <code>a59</code> bit(1) NOT NULL default '\0', <code>a60</code> bit(1) NOT NULL default '\0', <code>a61</code> bit(1) NOT NULL default '\0', <code>a62</code> bit(1) NOT NULL default '\0', <code>a63</code> bit(1) NOT NULL default '\0', <code>a64</code> bit(1) NOT NULL default '\0', <code>a65</code> bit(1) NOT NULL default '\0', <code>a66</code> bit(1) NOT NULL default '\0', <code>a67</code> bit(1) NOT NULL default '\0', <code>a68</code> bit(1) NOT NULL default '\0', <code>a69</code> bit(1) NOT NULL default '\0', <code>a70</code> bit(1) NOT NULL default '\0', <code>a71</code> bit(1) NOT NULL default '\0', <code>a72</code> bit(1) NOT NULL default '\0', <code>a73</code> bit(1) NOT NULL default '\0', <code>a74</code> bit(1) NOT NULL default '\0', <code>a75</code> bit(1) NOT NULL default '\0', <code>a76</code> bit(1) NOT NULL default '\0', <code>a77</code> bit(1) NOT NULL default '\0', <code>a78</code> bit(1) NOT NULL default '\0', <code>a79</code> bit(1) NOT NULL default '\0', <code>a80</code> bit(1) NOT NULL default '\0', <code>a81</code> bit(1) NOT NULL default '\0', <code>a82</code> bit(1) NOT NULL default '\0', <code>a83</code> bit(1) NOT NULL default '\0', <code>a84</code> bit(1) NOT NULL default '\0', <code>a85</code> bit(1) NOT NULL default '\0', <code>a86</code> bit(1) NOT NULL default '\0', <code>a87</code> bit(1) NOT NULL default '\0', <code>a88</code> bit(1) NOT NULL default '\0', <code>a89</code> bit(1) NOT NULL default '\0', <code>a90</code> bit(1) NOT NULL default '\0', <code>a91</code> bit(1) NOT NULL default '\0', <code>a92</code> bit(1) NOT NULL default '\0', <code>a93</code> bit(1) NOT NULL default '\0', <code>a94</code> bit(1) NOT NULL default '\0', <code>a95</code> bit(1) NOT NULL default '\0', <code>a96</code> bit(1) NOT NULL default '\0', <code>a97</code> bit(1) NOT NULL default '\0', <code>a98</code> bit(1) NOT NULL default '\0', <code>a99</code> bit(1) NOT NULL default '\0', <code>a100</code> bit(1) NOT NULL default '\0', <code>a101</code> bit(1) NOT NULL default '\0', <code>a102</code> text NOT NULL, <code>a103</code> int(11) NOT NULL, <code>a104</code> enum('a','n','r') NOT NULL, <code>a105</code> timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (<code>id</code>)<code> ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;</code> <hr> Thanks!
Tags
<mysql>
Title
which indexes multiple field?
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USitd
UserOwnerUserId
1. USitd
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. COWhat are you storing that could possible require such a hidious schema?
 singulars
 PostPostId
 POwhich indexes multiple field?
 UserUserId
 USthomasfedb

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.