Performance

[Bruno B . B . Magalhães]

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]