Note, the database is MySQL
Let me explain:
I am trying to do a report on the transactions in the database where I do a breakdown by groups. The way I have my database tables setup is roughly this:
transactions table
This table contains EVERY single transaction in the system, even if a client purchases three items, they all go in as individual transaction data, so you can see to the minutest detail.
It has these records:
Code: Select all
id int(11)
type varchar(50)
type_id varchar(10)
price varchar(10)
date
notes varchar(255)
cust_id int(11)
employee_id int(11)
pos_history_id int(11)Code: Select all
1 services 2 38 2005-03-31 First Client, free gift of Ready to Wear 2 2 1
2 stock 20 0 2005-03-31 First Client, free gift of Ready to Wear 2 2 1
3 services 2 15 2005-03-31 Colour did not take, not charged for it, to rebook... 3 2 1
4 services 7 0 2005-03-31 Colour did not take, not charged for it, to rebook... 3 2 1
5 services 3 19 2005-03-31 9 2 1
6 services 2 38 2005-03-31 Gift Voucher used ($38) 16 2 1
7 services 4 7 2005-03-31 6 2 1
8 services 2 38 2005-03-31 6 2 1
9 services 2 38 2005-03-31 12 2 1
10 services 6 27 2005-03-31 12 2 1
11 services 2 38 2005-03-31 13 2 1
12 services 7 62 2005-03-31 13 2 1
13 stock 32 22.95 2005-03-31 13 2 1There are nearly 7000 transactions in that table.
grouping table
This table contains the NAMES of the groups
It has these records:
Code: Select all
group_id group_name group_type
13 Hulabaloo stock
14 Beauty Products stock
15 Beauty Services services
16 Beauty Rent other
17 Cutting services
18 Colour services
19 Training services
20 Treatments services
25 Makeup stock
26 Makeup Service servicesgroups table
This table contains the actual group ITEMS.
the table looks like this:
Code: Select all
id group_id type type_id
97 13 stock 116
98 14 stock 53
108 15 services 31
99 15 services 24
67 15 services 22
87 15 services 25
72 15 services 27
71 15 services 23
70 15 services 21
69 15 services 20
68 15 services 26
100 16 other 1
107 17 services 14
106 17 services 30
105 17 services 32
104 17 services 1Now that you have the info, here is what I'm trying to do... get ready and I hope you can stay with me
I am trying to have a report that looks through the transactions table and displays the totals etc, based on the groups that the user has created. At the end of each days worth of data collection (and there may be months worth of data, looped through) I would expect the report to show each of the groups, ie:
Code: Select all
Hulabaloo, Beauty Products, Beauty Services, Beauty Rent, Cutting, Colour, Training, Treatments, services, Makeup, Makeup ServiceCode: Select all
Beauty Products: 14 Sold, $250 total, 15 July 2006Now you know what I WANT to do, I'd like to show you what I did do and how much of a workout it gives my database (and hence the machine). Using this method below, the machine ground to a halt for minutes on a FOUR day query (which might have been about 120 transactions total).
THE CURRENT CONCEPT:
1 loop through transactions table within a defined daterange (say, 1st of September to Today)
2 within that loop, loop through grouping table, one item at a time
3 for each grouping item, loop through the groups tables find a match of group_id's so we know the individual items we'll be searching for in the transaction table for that day
4 for each matching item in the grouping/groups tables (step 3), Loop through the transactions table for just that day we are in in loop 1 (transactions) and display the results.
Another way to say it:
IE, it might work like this:
Code: Select all
-Loop through transaction table, so at record 1, we:
--jump into the grouping table, so at record 1 of grouping we:
---jump into the groups table, so at record 1 of groups (if a match) we:
----jump into the transactions table for just that day and we
-----draw each record on screen that matches our criterea (ie: has same type and type_id as the groups table item)-10 grouping names (500 x 10 = 5000 database calls)
-about 80 group items (5000 x 80 = 400,000 database calls)
-about 25 transactions in a day say (400,000 x 25 = 10,000,000 database calls)
and you can see why the database is crawling to a halt.
Now maybe my logic is not quite correct with my above math, but something is happening and it's killing it!
How do I go about making it work more efficiently. I am wondering if joins are anything to do with it, and if so, lord help me
But I'm open to any help as I'm desperate to get this report working. There is a bookkeeper going NUTS for the report, and a taxman going even more nuts
Any help GREATLY appreciated and thank you so much for reading this.
Rob