sql - How to optimize a MySQL table? -

this question has answer here:

• optimizing simple mysql select on large table (75m+ rows) 2 answers

i'm looking way improve performance of table. below mysql table.

+----------+-------------+------+-----+---------+----------------+ | field    | type        | null | key | default |          | +----------+-------------+------+-----+---------+----------------+ | receiver | varchar(15) | no   |     | null    |                | | depcode  | varchar(12) | no   |     | null    |                | | sms      | text        | yes  |     | null    |                | | billable | varchar(5)  | yes  |     | null    |                | | smsc     | varchar(10) | yes  |     | null    |                | | senddate | date        | yes  |     | null    |                | | sendtime | time        | yes  |     | null    |                | | id       | int(11)     | no   | pri | null    | auto_increment | +----------+-------------+------+-----+---------+----------------+ 

it has got more 10,000,000 records.

below sql query used fetch records. ${variable} syntax variable.

select count(*) totalcount `tbl_incoming`  `depcode`=${depcode} , `smsc`=${smsc_value} , receiveddate between  ${fromdate} , ${todate}  order receiveddate, receivedtime; 

please see explain below.

explain select * `tbl_incoming`   `depcode`="slrd" , `smsc`="dgsm" , receiveddate  between  "2009-11-26" , "2014-11-26"   order receiveddate, receivedtime;  +----+-------------+--------------+------+---------------+------+---------+------+--------+-----------------------------+ | id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   |                       | +----+-------------+--------------+------+---------------+------+---------+------+--------+-----------------------------+ |  1 | simple      | tbl_incoming |  | null          | null | null    | null | 542527 | using where; using filesort | +----+-------------+--------------+------+---------------+------+---------+------+--------+-----------------------------+ 

according knowledge indexing can improve performance of table.

ps : i'm looking other options except indexing.

how can improve performance of table?

indexes should you.

here in query, having condition on columns depcode, smsc , receiveddate. must having more knowldge data. first identity column have number of distinct values among above 3. choose index column , add other 2 in include.

eg: create index ix_custome on tbl_incoming (depcode) include (smsc,receiveddate) 

this approach, ignore if not useful.