MySQL Forums
Forum List  »  Optimizer & Parser

Can I speed this up?
Posted by: Eljakim Schrijvers
Date: February 17, 2007 11:18AM

Hi there,

I have a query that takes a surprising long time (>2 seconds)

select btw_omschrijving, sum(boe_prijs_netto / btw_percentage) /100.0, sum(boe_prijs_netto / btw_percentage * (btw_percentage - 1) )/100.0,
sum(boe_prijs_netto) /100.0
from tblbtw
left join tblartikel on art_btw_id = btw_id 
left join tblartikelinstance on ari_art_id = art_id
left join tblartikelverhuur on avh_ari_id = ari_id
left join tblboeking on (boe_avh_id = avh_id or  boe_art_id = art_id) and boe_zle_id = 107
group by btw_id
having sum(boe_prijs_netto * btw_percentage) is not null

The output of explain is as follows:
mysql> explain select btw_omschrijving, sum(boe_prijs_netto / btw_percentage) /100.0, sum(boe_prijs_netto / btw_percentage * (btw_percentage - 1) )/100.0, sum(boe_prijs_netto) /100.0 from tblbtw left join tblartikel on art_btw_id = btw_id  left join tblartikelinstance on ari_art_id = art_id left join tblartikelverhuur on avh_ari_id = ari_id left join tblboeking on (boe_avh_id = avh_id or  boe_art_id = art_id) and boe_zle_id = 107 group by btw_id having sum(boe_prijs_netto * btw_percentage) is not null;
+----+-------------+--------------------+-------+----------------------------------+------------+---------+-------------------------------------+------+-------------+
| id | select_type | table              | type  | possible_keys                    | key        | key_len | ref                                 | rows | Extra       |
+----+-------------+--------------------+-------+----------------------------------+------------+---------+-------------------------------------+------+-------------+
|  1 | SIMPLE      | tblbtw             | index | NULL                             | PRIMARY    | 4       | NULL                                |    3 |             |
|  1 | SIMPLE      | tblartikel         | ref   | art_btw_id                       | art_btw_id | 4       | eljakassa.tblbtw.btw_id             |   26 | Using index |
|  1 | SIMPLE      | tblartikelinstance | ref   | ari_art_id                       | ari_art_id | 4       | eljakassa.tblartikel.art_id         |   10 | Using index |
|  1 | SIMPLE      | tblartikelverhuur  | ref   | avi_ari_id                       | avi_ari_id | 5       | eljakassa.tblartikelinstance.ari_id |  116 | Using index |
|  1 | SIMPLE      | tblboeking         | ref   | boe_zle_id,boe_avh_id,boe_art_id | boe_zle_id | 5       | const                               |   96 |             |
+----+-------------+--------------------+-------+----------------------------------+------------+---------+-------------------------------------+------+-------------+
5 rows in set (0.04 sec)

And the tables look as follows:
mysql> desc tblbtw;
+------------------+--------------+------+-----+-------------------+----------------+
| Field            | Type         | Null | Key | Default           | Extra          |
+------------------+--------------+------+-----+-------------------+----------------+
| btw_id           | int(11)      | NO   | PRI | NULL              | auto_increment |
| btw_ts           | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| btw_percentage   | decimal(5,2) | NO   | MUL |                   |                |
| btw_omschrijving | varchar(100) | YES  |     | NULL              |                |
+------------------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.03 sec)

mysql> desc tblartikel;
+------------------+--------------+------+-----+-------------------+----------------+
| Field            | Type         | Null | Key | Default           | Extra          |
+------------------+--------------+------+-----+-------------------+----------------+
| art_id           | int(11)      | NO   | PRI | NULL              | auto_increment |
| art_ts           | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| art_omschrijving | varchar(100) | YES  |     | NULL              |                |
| art_afkorting    | varchar(50)  | YES  |     | NULL              |                |
| art_ars_id       | int(11)      | NO   | MUL |                   |                |
| art_arg_id       | int(11)      | NO   | MUL |                   |                |
| art_btw_id       | int(11)      | NO   | MUL |                   |                |
+------------------+--------------+------+-----+-------------------+----------------+
7 rows in set (0.03 sec)

mysql> desc tblartikelinstance;
+------------------+--------------+------+-----+-------------------+----------------+
| Field            | Type         | Null | Key | Default           | Extra          |
+------------------+--------------+------+-----+-------------------+----------------+
| ari_id           | int(11)      | NO   | PRI | NULL              | auto_increment |
| ari_ts           | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| ari_switch_id    | int(11)      | YES  |     | NULL              |                |
| ari_art_id       | int(11)      | NO   | MUL |                   |                |
| ari_omschrijving | varchar(255) | NO   |     |                   |                |
+------------------+--------------+------+-----+-------------------+----------------+
5 rows in set (0.03 sec)

mysql> desc tblartikelverhuur;
+-------------+-----------+------+-----+-------------------+----------------+
| Field       | Type      | Null | Key | Default           | Extra          |
+-------------+-----------+------+-----+-------------------+----------------+
| avh_id      | int(11)   | NO   | PRI | NULL              | auto_increment |
| avh_ts      | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
| avh_start   | datetime  | YES  |     | NULL              |                |
| avh_eind    | datetime  | YES  |     | NULL              |                |
| avh_ari_id  | int(11)   | YES  | MUL | NULL              |                |
| avh_bon_id  | int(11)   | YES  |     | NULL              |                |
| avh_minuten | int(11)   | YES  |     | -1                |                |
| avh_arp_id  | int(11)   | YES  |     | NULL              |                |
+-------------+-----------+------+-----+-------------------+----------------+
8 rows in set (0.03 sec)

mysql> desc tblboeking;
+-----------------+-----------+------+-----+-------------------+----------------+
| Field           | Type      | Null | Key | Default           | Extra          |
+-----------------+-----------+------+-----+-------------------+----------------+
| boe_id          | int(11)   | NO   | PRI | NULL              | auto_increment |
| boe_ts          | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
| boe_aantal      | int(11)   | NO   |     |                   |                |
| boe_kale_prijs  | int(11)   | NO   |     |                   |                |
| boe_prijs_bruto | int(11)   | NO   |     |                   |                |
| boe_prijs_netto | int(11)   | NO   | MUL |                   |                |
| boe_datum       | datetime  | NO   |     |                   |                |
| boe_bon_id      | int(11)   | NO   | MUL |                   |                |
| boe_mod_id      | int(11)   | YES  | MUL | NULL              |                |
| boe_zle_id      | int(11)   | YES  | MUL | NULL              |                |
| boe_bed_id      | int(11)   | NO   | MUL |                   |                |
| boe_avh_id      | int(11)   | YES  | MUL | NULL              |                |
| boe_art_id      | int(11)   | YES  | MUL | NULL              |                |
| boe_tar_id      | int(11)   | NO   | MUL |                   |                |
| boe_kas_id      | int(11)   | NO   | MUL |                   |                |
+-----------------+-----------+------+-----+-------------------+----------------+
15 rows in set (0.03 sec)

One more bit of data:

mysql> select count(*) from tblbtw;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from tblartikel;
+----------+
| count(*) |
+----------+
|      105 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from tblartikelinstance;
+----------+
| count(*) |
+----------+
|       20 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from tblartikelverhuur;
+----------+
| count(*) |
+----------+
|     4650 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from tblboeking;
+----------+
| count(*) |
+----------+
|    29262 |
+----------+
1 row in set (0.04 sec)

Is there anything I can do to speed up this query, or will I have to just live with it?

Eljakim

Options: ReplyQuote


Subject
Views
Written By
Posted
Can I speed this up?
5322
February 17, 2007 11:18AM
3087
February 17, 2007 11:23AM
2905
February 25, 2007 11:40PM
2982
March 10, 2007 01:23PM
2855
March 11, 2007 12:59AM
2827
April 26, 2007 01:04AM
2788
August 30, 2007 02:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.