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.

The below query is taking a lot of time, more than an hour.

Is there anything I could to to tune it, server-wise or query-wise? Its just 23000 records.

EXPLAIN SELECT
    bc.charge
  , DATE(bq.`addeddate`) AS revenuedate
  , COUNT(*) AS 'subcount'
FROM
    billingqueue AS bq
  , billingcharge AS bc   
  , subscriptionqueue AS sq
  , keyword AS k
  , service AS ser
  , vendor AS v
  , region AS r
  , subscription AS s
WHERE
    (bq.`addeddate` BETWEEN '2012-10-12 00:00:00' AND '2012-10-18 23:59:59')
AND v.id = 1
AND bq.chargetype = 'subscription'
AND bq.status = 'success'
AND bq.transactionid = bc.transactionid
AND sq.`transactionid` = bq.transactionid
AND k.`idkeyword` = sq.`keywordid`
AND sq.`serviceid` = ser.`idservice`     
AND bq.vendorid = v.id
AND s.idregion = r.idregion
AND bc.`status` = 'success';
+----+-------------+-------+--------+----------------------------------------- ----------------------------+----------------+---------+-------------------------- --+-------+--------------------------------+
| id | select_type | table | type   | possible_keys                                                          | key            | key_len | ref                        | rows  | Extra                           |
+----+-------------+-------+--------+-----------------------------------------   ----------------------------+----------------+---------+--------------------------  --+-------+--------------------------------+
|  1 | SIMPLE      | v     | const  | PRIMARY                                                               | PRIMARY        | 4       | const                      |     1 | Using index                     |
|  1 | SIMPLE      | r     | index  | PRIMARY                                                             | PRIMARY        | 4       | NULL                       |     1 | Using index                    |
|  1 | SIMPLE      | ser   | index  | PRIMARY                                                              | PRIMARY        | 8       | NULL                       |   193 | Using index; Using join buffer |
|  1 | SIMPLE      | sq    | ref    | NewIndex1,NewIndex2,NewIndex3                                       | NewIndex2      | 8       | sm2.ser.idservice          |  3090 |                                |
|  1 | SIMPLE      | k     | eq_ref | PRIMARY                                                               | PRIMARY        | 8       | sm2.sq.keywordid           |     1 | Using index                       |
|  1 | SIMPLE      | bq    | eq_ref |  PRIMARY,NewIndex1,NewIndex2,NewIndex4,NewIndex5,NewIndex6,NewIndex7 | PRIMARY          | 103     | sm2.sq.transactionid,const |     1 | Using where                    |
|  1 | SIMPLE      | bc    | ref    | NewIndex2,NewIndex1                                                  | NewIndex2      | 103     | sm2.bq.transactionid       |     1 | Using where                      |
|  1 | SIMPLE      | s     | ref    | idx_sub_region                                                      | idx_sub_region | 5       | sm2.r.idregion             | 30802 | Using where;  Using index       |
+----+-------------+-------+--------+---------------------------------------------------------------------+----------------+---------+----------------------------+-------+--------------------------------+

Below is my my.cnf on a VM with 2 GB RAM. All tables are using InnoDB and the version is 5.5.28.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

symbolic-links=0

character-set-server         = utf8
collation-server         = utf8_general_ci
port                 = 3306
socket                 = /var/lib/mysql/mysql.sock
pid-file             = /var/lib/mysql/mysql.pid
skip-external-locking
skip-name-resolve
datadir                = /var/lib/mysql
relay_log            = mysql-relay-bin
relay_log_index            = mysql-relay-index
log_error            = mysql-error.err
log_warnings
log_bin                = mysql-bin
log_slow_queries        = mysql-slow.log
long_query_time         = 10    
max_binlog_size         = 256M    
expire_logs_days         = 4    
thread_stack             = 5M      
sort_buffer_size         = 128M      
read_buffer_size         = 128M      
read_rnd_buffer_size     = 128M      
join_buffer_size         = 1024M      
binlog_cache_size         = 128K      
query_cache_type = 0

max_connections         = 25    
max_connect_errors         = 5        
concurrent_insert        = 2        
connect_timeout            = 30    
max_allowed_packet         = 32M    
sql_mode             = NO_AUTO_CREATE_USER
max_heap_table_size         = 128M    
bulk_insert_buffer_size     = 128M    
tmp_table_size              = 128M 
thread_concurrency        = 16  
thread_cache_size        = 100 
key_buffer            = 512K    

innodb_data_home_dir            = /var/lib/mysql
innodb_data_file_path        = ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size        = 128M
innodb_log_files_in_group    = 2
innodb_buffer_pool_size        = 768M
innodb_additional_mem_pool_size    = 4M
innodb_flush_log_at_trx_commit    = 2
innodb_table_locks        = 0
innodb_log_buffer_size         = 32M
innodb_lock_wait_timeout     = 60    
innodb_thread_concurrency    = 16
innodb_commit_concurrency    = 16
innodb_support_xa        = 0
skip-innodb-doublewrite

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
share|improve this question
    
Thank you very much for all this information. Do you have a question for us to answer? –  ppeterka Oct 19 '12 at 7:34
    
sorry for the typo –  Mannoj Oct 19 '12 at 7:38
2  
This is a nice big Cartesian product of (r joined to s) and (all other tables). Do you really want this? –  dezso Oct 19 '12 at 9:14
    
@dezso: This should be an answer. I also notice that there is a COUNT(*) and no GROUP BY so the query will return only one row and I doubt that this is wanted. –  ypercube Oct 19 '12 at 13:43
add comment

1 Answer 1

up vote 3 down vote accepted

Your query is a nice big Cartesian product of (r joined to s) and (all other tables). This means that you will have (COUNT(*) FROM r JOIN s) * (COUNT(*) FROM the_other_join) rows, which can be a really, really big number (say 23000 * 23000 in your case, which is around 500 million) instead of what you expect.

Do you really want this?

This is a good example of the value of using ANSI joins: that way you get a syntax error if you forget to specify the link between two tables. (Still one can link the wrong tables, I know...) With this syntax your query would look like

...
JOIN vendor AS v ON bq.vendorid = v.id
JOIN region AS r -- here is the error
JOIN subscription AS s ON s.idregion = r.idregion
...
share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.