I am using a MYSQL 4.0.20-standard for a point of sale system.the following table is the main focus of all the transaction:
Quote
CREATE TABLE `poscustomerinvoice` (
`orderid` varchar(255) NOT NULL default '0',
`invoicenumber` varchar(255) NOT NULL default '',
`tillid` int(11) NOT NULL default '0',
`empid` int(11) NOT NULL default '0',
`invoicedate` datetime NOT NULL default '0000-00-00 00:00:00',
`currentdate` date default NULL,
`actualqty` int(11) NOT NULL default '0',
`actualamount` double(11,2) NOT NULL default '0.00',
`finalize` int(11) NOT NULL default '0',
`paymode` int(11) default '0',
`invdiscount` double(11,2) NOT NULL default '0.00',
`invporoft` double(11,2) NOT NULL default '0.00',
`void` char(1) NOT NULL default '',
`specialoffer` double(11,2) NOT NULL default '0.00',
KEY `OrderId` (`orderid`)
) TYPE=InnoDB;
This table has about 40,000 records and growing with good pace,table requires a great deal of searching on `invoicedate` column.Please help me out to optimize the table design,what can I do to maximize performance on `invoicedate` based searches.(Table has already 40,000 records).and also please give me some suggestions that I should take for fast growing tables(large insertions),
Thanks a lot,