Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this query

select counterparty_id, 
       max(id) max_id 
from client_administrations 
where counterparty_type = 1 
  and effective_on <= '2013-10-22' 
group by counterparty_id

When I explain this query it gives me

************* 1. row *************
id: 1
select_type: SIMPLE
table: client_administrations
type: index
possible_keys: NULL
key: cec
key_len: 11
ref: NULL
rows: 234969
Extra: Using where; Using index
1 row in set (0.00 sec)

this query returns 2,34,630 rows which takes 0.18 seconds to run. But the problem is this query is used many times in a given functionality and the rows in the database might go upto 10 lakhs even. That is my concern. I tried profiling for this

mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000126 |
| checking permissions | 0.000018 |
| Opening tables | 0.000032 |
| System lock | 0.000016 |
| init | 0.000051 |
| optimizing | 0.000029 |
| statistics | 0.000145 |
| preparing | 0.000047 |
| executing | 0.000010 |
| Sorting result | 0.000009 |
| Sending data | 0.117429 |
| end | 0.000024 |
| query end | 0.000007 |
| closing tables | 0.000012 |
| freeing items | 0.000016 |
| logging slow query | 0.000004 |
| cleaning up | 0.000005 |
+----------------------+----------+

This shows sending data takes maximum amount of time. The RAM size in my localhost is 3GB and in server it is 32 GB. EVen this makes no difference in the time taken for query to run. Its not only for this query but there are several queries in my database which inspite of having index in the "extra" column of explain the maximum time is taken in sending data of profiling. I did change some global variables. Here is the default setting of my global variables

mysql> show variables like "%cache%";
+------------------------------+----------------------+
| Variable_name | Value |
+------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 400 |
| table_open_cache | 400 |
| thread_cache_size | 8 |
+------------------------------+----------------------+

Can something be done to help this "sending data " part. Any help would be greatly appreciated

Edit:-

Added show create table client_administrations

client_administrations | CREATE TABLE `client_administrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `counterparty_type` int(11) NOT NULL,
  `counterparty_id` int(11) NOT NULL,
  `administered_at` int(11) NOT NULL,
  `registered_at` int(11) NOT NULL,
  `effective_on` date NOT NULL,
  `performed_by` int(11) NOT NULL,
  `recorded_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT '2013-01-06 14:29:13',
  PRIMARY KEY (`id`),
  KEY `cec` (`counterparty_id`,`counterparty_type`,`effective_on`)
) ENGINE=InnoDB AUTO_INCREMENT=234635 DEFAULT CHARSET=utf8 
share|improve this question
    
Run SHOW CREATE TABLE client_administrations; and add the output in the question, please. –  ypercube Oct 23 '13 at 5:17
    
Just added it. Please check –  user2294477 Oct 23 '13 at 5:24
    
Does the query return as many rows as the table have (around 234630)? If so, that indicates that the counterparty_id could have a UNIQUE constraint - and then you could remove the GROUP BY. –  ypercube Oct 23 '13 at 5:40
    
This scenario has a such that it is unique right now. But actually a counterparty_id can have many ids ...id is auto-increment. Basically using this query we are getting max(id) for each counterparty_id –  user2294477 Oct 23 '13 at 5:48
    
I would use a (counterparty_type, counterparty_id, effective_on) I don't expect it to be much more efficient with the current data (it seems all the rows have counterparty_type=1 now) but it's better index for your query. –  ypercube Oct 23 '13 at 5:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.