List:General Discussion« Previous MessageNext Message »
From:Bruno B. B. Magalhães Date:April 22 2008 3:41pm
Subject:Performance
View as plain text  
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
Thread
auto_incrementHiep Nguyen21 Apr
  • Re: auto_incrementSebastian Mendel22 Apr
    • Re: auto_incrementSebastian Mendel22 Apr
      • Re: auto_incrementHiep Nguyen22 Apr
  • Re: auto_incrementBen Clewett22 Apr
    • Re: auto_incrementSebastian Mendel22 Apr
      • Re: auto_incrementBen Clewett22 Apr
      • PerformanceBruno B .  B .  Magalhães22 Apr
        • Re: PerformancePhil22 Apr
        • Re: PerformanceRob Wultsch22 Apr
        • Re: PerformanceRob Wultsch22 Apr
        • Re: PerformancePerrin Harkins22 Apr
          • Re: PerformanceBruno B .  B .  Magalhães22 Apr
            • Re: PerformanceRob Wultsch22 Apr