PDA

View Full Version : Multiply joined sql tables makes it all very slow


ABC123

Lance Gurd
02-08-2012, 12:05 PM
Backend MySQL.

Senario: Quality Management Review Meeting. We have one of these every 6 months and I have created tables to record the items discussed. Header table just has date of meeting, attendees in a long text field, date of next meeting and of course the meeting id.

There are 11 sections that need to be discussed in the meetings and each of these can have multiple items per meeting, therefore each is a seperate child table with its own section id and the meeting id and then the discussed item, who needs to action it and by when. So we have 11 child tables each with 5 fields all linked to header table with 4 fields.

All tables are linked by an inner join.

The result set for the last 3 meetings is 4097 records! and is very slow to generate.

Does anybody have a simpler solution for recording meeting minutes?

Steve Workings
02-08-2012, 03:26 PM
Do you have indexes for the foreign keys? I had a similar problem a while ago, and when I created all the correct indexes everything performed very nicely.

Lance Gurd
02-09-2012, 03:18 AM
Hi Steve,

Still plowing through the latest great video keep up the great work.

Yes all the foreign keys are indexed. Think I may have to give this idea up, trying to sort out the report to print it all out is a nightmare as well

Lance Gurd
02-09-2012, 01:17 PM
Thinking outside the box is the obvious answer to this!

1 child table with a field for the section of the report is the way to go :grin: