On Tue, Apr 22, 2008 at 8:41 AM, Bruno B. B. Magalhães < [EMAIL PROTECTED]> wrote:
> Hi everybody, > > I am back to this list after a long period away due to work time > restrictions... I have great news and a few interesting applications that I > will release to the mysql community very soon, most probably as open source. > > But now I have a performance problem with a client of mine, that I was not > able to solve... The problem is that I have a very large table in terms of > data, about 7.000.000 financial transactions records, with the following > table (translated from portuguese): > > CREATE TABLE `transactions` ( > `client_id` int(5) unsigned zerofill NOT NULL default '00000', > `client_unit_id` int(4) unsigned zerofill NOT NULL default '0000', > `client_property_id` int(6) unsigned zerofill NOT NULL default '000000', > `transaction_id` int(6) unsigned zerofill NOT NULL default '000000', > `transaction_account_id` int(3) unsigned zerofill NOT NULL default '000', > `transaction_classification_id` int(3) unsigned NOT NULL default '0', > `transaction_category_id` int(4) unsigned zerofill NOT NULL default > '0000', > `transaction_complement` varchar(200) NOT NULL, > `transaction_date` date default NULL, > `transaction_amount` decimal(16,2) NOT NULL, > `transaction_parcel` varchar(8) NOT NULL, > `transaction_nature` varchar(1) NOT NULL > KEY `transactions_idx_1` > (`client_id`,`client_unit_id`,`client_property_id`,`transaction_account_id`, > > > `transaction_classification_id`,`transaction_category_id`,`transaction_id`,`transaction_date`,`transaction_nature`) > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 > > And most the queries are similar to this one: > > SELECT UNIX_TIMESTAMP(transactions.transaction_date) AS date, > transactions.transaction_complement AS complement, > transactions.transaction_parcel AS parcel, > transactions.transaction_amount AS amount, > transactions.transaction_nature AS nature, > transactions_categories.transaction_category_description > AS category_description > FROM transactions AS transactions > LEFT JOIN transactions_categories AS transactions_categories > ON transactions.transaction_category_id = > transactions_categories.transaction_category_id > WHERE transactions.client_id = :client > AND transactions.client_unit_id = :unit > AND transactions.transaction_date >= :start_date > AND transactions.transaction_date <= :stop_date > ORDER BY transactions.transaction_date, > transactions.transaction_id ASC > > So the most important indexes are client_id , client_unit_id , > client_property_id , transaction_account_id , transaction_classification_id > , transaction_category_id , transaction_id , transaction_date , > transaction_nature, and most of the time they are called together, I thing > the most problematic part of those queries are the date range part, should I > use a different index only for this column to maintain the index small? > > Most of the financials reports today takes about 8 to 12 seconds to be > generated for one month (course that I have to sum previous months totals to > give the balance). > > Thanks in advance... > > Regards, > Bruno B B Magalh'aes > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > I would think probably not on the date range. An Phil says an EXPLAIN would probably be helpful. Particularly of note would be the key_len. I would image an optimal index would be something like KEY `transactions_idx_1` (`client_id`,`client_unit_id`,`transaction_date`) If memory serves MySQL does not use a composite index to the right of range scan. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim)
