Adding composite indexes on MYSQL table

I have a table like this

CREATE TABLE IF NOT EXISTS `billing_success` (
`bill_id` int(11) NOT NULL AUTO_INCREMENT,
`msisdn` char(10) NOT NULL,
`circle` varchar(2) NOT NULL,
`amount` int(11) NOT NULL,
`reference_id` varchar(100) NOT NULL,
`source` varchar(100) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`bill_id`),
KEY `msisdn` (`msisdn`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8573316 ;

and I want to add composite indexes to optimize queries. This is the table from where I generate reports, lately its becoming very slow. These are my queries to generate reports

1.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='XX' OR source='Y' OR source='STR') AND (t开发者_开发技巧ime like '$date%')

2.SELECT msisdn,amount,circle FROM billing_total_success WHERE source <> 'RNH' AND source <> 'STR' AND source <> 'XAS' AND source <> 'RTR' AND (time like '$date%')

3.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='STR' OR source='RER' OR source='ASD') AND time BETWEEN  '$date1' AND '$date2'

4.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='RNH') AND time BETWEEN  '$date1' AND '$date2'

Please tell me where all should I add indexes for these queries to be optimized and how to add proper index given the query.

A. You probably want an index on the timestamp field.

However this isn't necessarily the end of the story; if your queries are range-scanning on the timestamp index, then having it as a secondary index is not necessarily a good idea in InnoDB.

B. Make timestamp the first part of the primary key

This is counter-intuitive, but as InnoDB clusters on the primary key, having the timestamp the first part of the primary key will make the secondary index range scan into a primary key range scan, which is generally better. A secondary index range scan needs to retrieve each row in the range; a primary key scan has retrieved it already.

C. Partition by time

If your queries are slow because the table is becoming too big for memory (IO reads are required often), but you are always querying a small(ish) time range, consider having daily or hourly partitions.

Of course daily partitions work a lot less well if your users are in different time zones, as their days overlap with the partitions' days.

Partitioning requires scripts to do ongoing maintenance on the tables (creating new partitions; dropping old ones). You need to provide and test these scripts so this is clearly an overhead for the developer. Operations also need to monitor these scripts, so they have operational overhead too.