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