There is a MySQL table with 39 columns and 1349827 rows. Here is the SHOW CREATE TABLE output:
CREATE TABLE `TRANSACTION_DETAIL` (
`TRANSACTION_DETAIL_ID` bigint(20) NOT NULL auto_increment COMMENT 'Primary key for TransactionDetail.',
`OBJECT_TYPE` varchar(255) NOT NULL COMMENT 'Used to identify different transactionDetail.',
`VERSION` bigint(20) NOT NULL,
`TRANSACTION_TYPE` varchar(255) default NULL COMMENT 'A transaction type of the transaction',
`TRANSACTION_CHANNEL` varchar(60) default NULL COMMENT 'A channel of the transaction',
`SOURCE_ADDRESS` varchar(120) default NULL COMMENT 'A source address of the transaction',
`TARGET_ADDRESS` varchar(120) default NULL COMMENT 'A target address of the transaction',
`CREDIT_AMOUT` decimal(19,2) default NULL,
`DEBIT_AMOUT` decimal(19,8) default NULL COMMENT 'The debit amount value of transaction.',
`COMMISSION_AMOUT` decimal(19,8) default NULL COMMENT 'The commission amount value of transaction.',
`TAX_AMOUT` decimal(19,8) default NULL COMMENT 'The tax amount value of transaction.',
`BONUS_AMOUT` decimal(19,8) default NULL COMMENT 'The bonus amount value of transaction.',
`TRANSACTION_ID` bigint(20) default NULL,
`SELECTED_IPG` varchar(60) default NULL COMMENT 'Selected IPG gateway for the transaction',
`CARD_TYPE` varchar(24) default NULL COMMENT 'Credit Card Type for the payment',
`STATUS` int(11) default NULL COMMENT 'Status of the transaction',
`STATUS_DESCRIPTION` varchar(255) default NULL COMMENT 'status description of the transaction',
`RECEIPT_ID` bigint(20) default NULL COMMENT 'receipt id of the transaction',
`SUBSCRIBER_TYPE` varchar(10) default NULL COMMENT 'subscriber type of the transaction',
`UPDATE_MODE` char(1) default NULL COMMENT 'update mode of the transaction',
`UPDATED_BY` varchar(255) default NULL COMMENT 'transaction updated by the user',
`IPG_TRANSACTION_MAINTENANCE_ID` bigint(20) default NULL,
`REMOTE_MERCHANT_TRX_REFERENCE_CODE` varchar(255) default NULL COMMENT 'remote merchant trx reference code for the transaction',
`MRECHARGE_TRX_ID` varchar(255) default NULL COMMENT 'mRechargeTrxId for the transaction',
`API_RESPONSE_CODE` int(11) default NULL COMMENT 'api response code for the transaction',
`LOCATION_ID` varchar(255) default NULL COMMENT 'locationId for the transaction',
`TERMINAL_ID` varchar(255) default NULL COMMENT 'terminalId for the transaction',
`RECEIPT_NUMBER` varchar(255) default NULL COMMENT 'receiptNumber for the transaction',
`PAYMENT_MODE` int(11) default NULL COMMENT 'paymentMode of the transaction',
`REMOTE_TRX_CREATED_TIME` datetime default NULL COMMENT 'The date/time when the remote trx was created.',
`EXTRA_PARAM_1` varchar(255) default NULL COMMENT 'extraParam1 for the transaction',
`EXTRA_PARAM_2` varchar(255) default NULL COMMENT 'extraParam2 for the transaction',
`EXTRA_PARAM_3` varchar(255) default NULL COMMENT 'extraParam3 for the transaction',
`EXTRA_PARAM_4` varchar(255) default NULL COMMENT 'extraParam4 for the transaction',
`EXTRA_PARAM_5` varchar(255) default NULL COMMENT 'extraParam5 for the transaction',
`EXTRA_PARAM_6` varchar(255) default NULL COMMENT 'extraParam6 for the transaction',
`IN_RESPONSE_CODE` varchar(10) default NULL COMMENT 'IN server response code received for the transaction',
`POSTPAID_RESPONSE_CODE` varchar(10) default NULL COMMENT 'Postpaid server response code received for the transaction',
`COMMENT` char(255) default NULL,
PRIMARY KEY (`TRANSACTION_DETAIL_ID`),
KEY `IDX_TRANSACTION_DETAIL_TRANSACTION_CHANNEL` (`TRANSACTION_CHANNEL`),
KEY `IDX_TRANSACTION_DETAIL_TARGET_ADDRESS` (`TARGET_ADDRESS`),
KEY `IDX_TRANSACTION_DETAIL_SOURCE_ADDRESS` (`SOURCE_ADDRESS`),
KEY `IDX_TRANSACTION_DETAIL_TRANSACTION_TYPE` (`TRANSACTION_TYPE`),
KEY `FKCFF930F2A7C5B155` (`TRANSACTION_ID`),
KEY `FKCFF930F25B936AA0` (`IPG_TRANSACTION_MAINTENANCE_ID`),
KEY `IDX_REMOTE_MERCHANT_TRX_ID` (`REMOTE_MERCHANT_TRX_REFERENCE_CODE`),
KEY `IDX_TRANSACTION_DETAIL_SUBSCRIBER_TYPE` (`SUBSCRIBER_TYPE`),
KEY `IDX_TRANSACTION_DETAIL_STATUS` (`STATUS`),
KEY `IDX_REMOTE_MERCHANT_TRX_CREATED_TIME` (`REMOTE_TRX_CREATED_TIME`),
KEY `IDX_TRANSACTION_DETAIL_CREDIT_AMOUNT` (`CREDIT_AMOUT`),
KEY `idx_trans_detail_object_type` (`OBJECT_TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='TransactionDetail represents a Transaction details';
I need to get total of values in one column for specific rows (1281298) only. I used sum function to get that total. Query time is around 2mins.
select sum(CREDIT_AMOUT) as "Total Credit Amount"
from TRANSACTION_DETAIL
where SOURCE_ADDRESS = 'XXXXXXXX' and
SUBSCRIBER_TYPE = 'POST' and
OBJECT_TYPE = 'XXXXXXXXXXXXXXX' and STATUS = 3
and REMOTE_TRX_CREATED_TIME between '2011-05-01 00:00:00' and '2012-12-31 23:59:59';
+---------------------+
| Total Credit Amount |
+---------------------+
| 113142678.69 |
+---------------------+
1 row in set (2 min 11.12 sec)
When I use an index, query time is around 25-30 secs.
create index total_credit_amount on TRANSACTION_DETAIL(CREDIT_AMOUT, SOURCE_ADDRESS, SUBSCRIBER_TYPE, OBJECT_TYPE, STATUS, REMOTE_TRX_CREATED_TIME);
select sum(CREDIT_AMOUT) as "Total Credit Amount" from TRANSACTION_DETAIL use index(total_credit_amount) where SOURCE_ADDRESS = 'XXXXXXX' and SUBSCRIBER_TYPE = 'POST' and OBJECT_TYPE = 'XXXXXXXXXXXX' and STATUS = 3 and REMOTE_TRX_CREATED_TIME between '2011-05-01 00:00:00' and '2012-12-31 23:59:59';
+---------------------+
| Total Credit Amount |
+---------------------+
| 113142678.69 |
+---------------------+
1 row in set (28.89 sec)
Explain output:
explain select sum(CREDIT_AMOUT) as "Total Credit Amount" from TRANSACTION_DETAIL use index(total_credit_amount) where SOURCE_ADDRESS = 'XXXXXX' and SUBSCRIBER_TYPE = 'POST' and OBJECT_TYPE = 'XXXXXXXXXXXXXXXXXX' and STATUS = 3 and REMOTE_TRX_CREATED_TIME between '2011-05-01 00:00:00' and '2012-12-31 23:59:59';
+----+-------------+--------------------+-------+---------------+---------------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------+---------------+---------------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | TRANSACTION_DETAIL | index | NULL | total_credit_amount | 414 | NULL | 1281298 | Using where; Using index |
+----+-------------+--------------------+-------+---------------+---------------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)
How do I can reduce query time to less than 3 secs?
Determine the cardinality of each of the columns in your WHERE clause
Construct your index with the columns listed from lowest cardinality to highest
If this doesn't reduce the time to the required level then you'll need to use a more powerful machine