which indexes multiple field?

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 firstindex (ad2,ad4)

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)

CREATE TABLE my_table (

id int(11) unsigned NOT NULL auto_increment,

a1 varchar(100) NOT NULL default '',

a2 varchar(100) NOT NULL default '',

a3 varchar(100) NOT NULL default '',

a4 varchar(100) NOT NULL default '',

a5 varchar(60) NOT NULL default '',

a6 varchar(60) NOT NULL default '',

a7 varchar(15) NOT NULL default '',

a8 varchar(100) NOT NULL default '',

a9 varchar(60) NOT NULL default '',

a10 varchar(20) NOT NULL default '',

a11 varchar(60) NOT NULL default '',

a12 varchar(60) NOT NULL default '',

a13 varchar(60) NOT NULL default '',

a14 varchar(100) NOT NULL default '',

a15 varchar(60) NOT NULL default '',

a16 varchar(60) NOT NULL default '',

a17 varchar(15) NOT NULL default '',

a18 varchar(100) NOT NULL default '',

a19 varchar(60开发者_StackOverflow) NOT NULL default '',

a20 varchar(11) NOT NULL default '',

a21 varchar(60) NOT NULL default '',

a22 varchar(60) NOT NULL default '',

a23 varchar(60) NOT NULL default '',

a24 varchar(60) NOT NULL default '',

a25 date NOT NULL,

a26 time NOT NULL,

a27 date NOT NULL,

a28 time NOT NULL,

a29 decimal(2,0) NOT NULL default '0',

a30 decimal(25,6) NOT NULL default '0.000000',

a31 bit(1) NOT NULL,

a32 decimal(2,0) NOT NULL default '0',

a33 decimal(52,6) NOT NULL default '0.000000',

a34 varchar(50) NOT NULL default '',

a35 varchar(50) NOT NULL default '',

a36 varchar(50) NOT NULL default '',

a37 varchar(50) NOT NULL default '',

a38 varchar(50) NOT NULL default '',

a39 varchar(50) NOT NULL default '',

a40 varchar(50) NOT NULL default '',

a41 bit(1) NOT NULL default '\0',

a42 bit(1) NOT NULL default '\0',

a43 bit(1) NOT NULL default '\0',

a44 bit(1) NOT NULL default '\0',

a45 bit(1) NOT NULL default '\0',

a46 bit(1) NOT NULL default '\0',

a47 bit(1) NOT NULL default '\0',

a48 bit(1) NOT NULL default '\0',

a49 bit(1) NOT NULL default '\0',

a50 bit(1) NOT NULL default '\0',

a51 bit(1) NOT NULL default '\0',

a52 bit(1) NOT NULL default '\0',

a53 bit(1) NOT NULL default '\0',

a54 bit(1) NOT NULL default '\0',

a55 bit(1) NOT NULL default '\0',

a56 bit(1) NOT NULL default '\0',

a57 bit(1) NOT NULL default '\0',

a58 bit(1) NOT NULL default '\0',

a59 bit(1) NOT NULL default '\0',

a60 bit(1) NOT NULL default '\0',

a61 bit(1) NOT NULL default '\0',

a62 bit(1) NOT NULL default '\0',

a63 bit(1) NOT NULL default '\0',

a64 bit(1) NOT NULL default '\0',

a65 bit(1) NOT NULL default '\0',

a66 bit(1) NOT NULL default '\0',

a67 bit(1) NOT NULL default '\0',

a68 bit(1) NOT NULL default '\0',

a69 bit(1) NOT NULL default '\0',

a70 bit(1) NOT NULL default '\0',

a71 bit(1) NOT NULL default '\0',

a72 bit(1) NOT NULL default '\0',

a73 bit(1) NOT NULL default '\0',

a74 bit(1) NOT NULL default '\0',

a75 bit(1) NOT NULL default '\0',

a76 bit(1) NOT NULL default '\0',

a77 bit(1) NOT NULL default '\0',

a78 bit(1) NOT NULL default '\0',

a79 bit(1) NOT NULL default '\0',

a80 bit(1) NOT NULL default '\0',

a81 bit(1) NOT NULL default '\0',

a82 bit(1) NOT NULL default '\0',

a83 bit(1) NOT NULL default '\0',

a84 bit(1) NOT NULL default '\0',

a85 bit(1) NOT NULL default '\0',

a86 bit(1) NOT NULL default '\0',

a87 bit(1) NOT NULL default '\0',

a88 bit(1) NOT NULL default '\0',

a89 bit(1) NOT NULL default '\0',

a90 bit(1) NOT NULL default '\0',

a91 bit(1) NOT NULL default '\0',

a92 bit(1) NOT NULL default '\0',

a93 bit(1) NOT NULL default '\0',

a94 bit(1) NOT NULL default '\0',

a95 bit(1) NOT NULL default '\0',

a96 bit(1) NOT NULL default '\0',

a97 bit(1) NOT NULL default '\0',

a98 bit(1) NOT NULL default '\0',

a99 bit(1) NOT NULL default '\0',

a100 bit(1) NOT NULL default '\0',

a101 bit(1) NOT NULL default '\0',

a102 text NOT NULL,

a103 int(11) NOT NULL,

a104 enum('a','n','r') NOT NULL,

a105 timestamp NOT NULL default CURRENT_TIMESTAMP,

PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Thanks!

Indexes should be created based on your queries, as you said and if I have not misunderstood You mostly apply query having where on a2 and a4..

so as u supposed you can use indexing on these two field.

and as u said in advanced query, you are using mostly all or many more fields.

you should not use indexing on this much fields as that will be not much effective than without any indexes.

as indexing on these much fields increase load on insert and delete queries.

so you can use indexing on mostly used fields like a2 and a4 or leave it.