Portal Home > Knowledgebase > Articles Database > MySql join 3 tables
MySql join 3 tables
| Posted by raulgonzalez, 03-13-2008, 01:08 PM |
| Hello,
I am trying to join 3 tables and display results on the browser. The statement below works, I get the results I want. The problem is that it takes about 15 seconds to display only 50 records. I am working this around by showing a please wait... with the sleep() function while everything else loads up.
I know that my query must need adjustments. Any ideas?
//students table has about 23000 records
//unique course has about 1000 records
//access has about 500 records
///////////////////////////////////////////////
$the_query = "SELECT DISTINCT ClassSection_id, InstructorName
FROM students
INNER JOIN unique_course ON
students.ClassSection_id=unique_course.course
INNER JOIN access ON
access.access=students.Department_id
WHERE
unique_course.active = '1' AND
unique_course.Done_Already = '0' AND
access.access_id = '{$employee_id}'
";
/////////////////////////////////////////////////
thank you
raul gonzalez
Last edited by raulgonzalez; 03-13-2008 at 01:17 PM.
|
| Posted by tnguy3n, 03-13-2008, 01:48 PM |
| how about indexing some fields in your tables, i.e. students.ClassSection_id, students.Department_id, unique_course.course, access.access
btw, wouldn't it be
instead of what it currently is?
|
| Posted by eviltechie, 03-20-2008, 01:28 PM |
| I'd agree that indexes would be important, if you don't have them already. I run join queries on larger tables than that without delays. If indexes already exist, perhaps rebuild them.
Maybe see if you can get rid of DISTINCT, as that can slow things down. Are you getting duplicate rows without it?
|
| Posted by raulgonzalez, 03-20-2008, 03:55 PM |
| Yes, I get duplicates if I dont use DISTINCT. The reason is that all the records are given to me in a flat file with lots of data repeating. Example:
Course | Instructor | Student | Department
---------------------------------------------------
Coruse1|Instructor1|Student1|Department1
Course1|Instructor1|Student2|Department1
Course2|Instructor1|Student1|Department1
So, I am given a record for every student in class which is about 23,000
A student can have more than one class
An instructor can teach more than one class
A class can have more than one instructor [at our campus]
I've done a lot of separation into multiple tables, but still need to go deeper.
By the way, I separated the above tables and I only need to join two now. It works great for now. Below is wha I am using.
///////////////////////////////////////////////
$the_query = "SELECT *
FROM access
INNER JOIN unique_course ON
access.access=unique_course.Department_id
WHERE
unique_course.active = '1' AND
unique_course.Done_Already = '0' AND
access.access_id = '{$employee_id}'
";
/////////////////////////////////////////////////
Last edited by raulgonzalez; 03-20-2008 at 03:59 PM.
|
Add to Favourites 
Print this Article
Also Read
