|
Pages: [1]
|
  |
|
|
Author
|
Topic: SQL question . . . (Read 63 times)
|
|
Ryo-Ohki
[Wumpa]
Board Admin
Karma: +30/-6
 Offline
Posts: 187
 
 
|
 |
SQL question . . .
« on: January 20, 2005, 08:11:07 AM »
|
|
So are there any good DBA's lurking around here? I think I'm pretty good but I'm completely stumped by this one. Here it is.
The query:
SELECT prefectures.prefectureID, prefectures.kanji, count(teachers_prefectures.teacherID) as total
FROM prefectures
INNER JOIN teachers_prefectures
USING(prefectureID)
INNER JOIN teachers_languages
ON teachers_languages.languageID = '14' and
teachers_languages.teacherID = teachers_prefectures.teacherID
INNER JOIN teachers
ON teachers.status = 'validated' and
teachers.teacherID = teachers_prefectures.teacherID
GROUP BY prefectures.prefectureID
|
|
The tables:
CREATE TABLE `teachers` (
`teacherID` smallint(6) NOT NULL auto_increment,
`lastmodified` datetime NOT NULL default '0000-00-00 00:00:00',
`photo` tinytext NOT NULL,
`age` tinyint(4) NOT NULL default '0',
`gender` tinytext NOT NULL,
`privateprice` smallint(6) NOT NULL default '0',
`country` tinytext NOT NULL,
`experience` tinyint(4) NOT NULL default '0',
`nativelanguage` tinyint(4) NOT NULL default '0',
`status` tinytext NOT NULL,
PRIMARY KEY (`teacherID`),
KEY `status` (`status`(6))
) ENGINE=MyISAM
CREATE TABLE `teachers_prefectures` (
`teacherID` smallint(6) NOT NULL default '0',
`prefectureID` smallint(6) NOT NULL default '0',
PRIMARY KEY (`teacherID`,`prefectureID`),
KEY `prefectureID` (`prefectureID`),
KEY `teacherID` (`teacherID`)
) ENGINE=MyISAM
CREATE TABLE `teachers_languages` (
`teacherID` smallint(6) NOT NULL default '0',
`languageID` smallint(6) NOT NULL default '0',
PRIMARY KEY (`teacherID`,`languageID`),
KEY `languageID` (`languageID`),
KEY `teacherID` (`teacherID`)
) ENGINE=MyISAM
CREATE TABLE `prefectures` (
`prefectureID` tinyint(4) NOT NULL auto_increment,
`prefecture` tinytext NOT NULL,
`kanji` tinytext NOT NULL,
`kana` tinytext NOT NULL,
PRIMARY KEY (`prefectureID`)
) ENGINE=MyISAM
|
|
The explain:
explain table
So there is some ugliness going on here. If anyone has any idea why I'd buy you a pizza.
|
|
 Logged
|
"how does this work? where do my teeth go?" - a lepord cub trying to eat a turtle
-- panda cam
|
|
|
Porter
[Wumpa]
Board Admin
Karma: +176/--88
Offline
Gender: 
Posts: 3910
|
|
Re:SQL question . . .
« Reply #1 on: January 20, 2005, 08:38:30 AM »
|
|
Could you fill us in a bit more about what you're trying to do, and what about it isn't working? I hate joins, but they're easier to understand if you've got some background of the application.
On a relevant note, since I'm the only one who has taken Knox's Databases course in my current CompSci class, I get to be the sole database guru. I have about 6 separate DBs from the client that need to be merged into one, with addition fields and relations added-- including writing an automated migration tool to DO this! It's a nightmare of a job for only 8 weeks.
|
|
Logged
|
[Wumpa] Porter
--Silent, professional, lethal... sometimes.
|
|
|
Ryo-Ohki
[Wumpa]
Board Admin
Karma: +30/-6
Offline
Posts: 187
|
|
Re:SQL question . . .
« Reply #2 on: January 20, 2005, 09:32:33 AM »
|
|
hahaha, yeah I've had to take on the role of DBA here too, it's kind of fun actually :-p I'm doing a huge DB redesign and migration myself. I might be switching us to Postgre pretty soon. You should be able to see my concern just by looking at the explain statement. I hate anything the mentions "filesort" or "temporary" and I seem to have picked up both of them. Now I know there are situations where you can't avoid these without table redesign so that could be my problem right there, but I'm hoping I just overlooked something.
|
|
Logged
|
"how does this work? where do my teeth go?" - a lepord cub trying to eat a turtle
-- panda cam
|
|
|
Ryo-Ohki
[Wumpa]
Board Admin
Karma: +30/-6
Offline
Posts: 187
|
|
Re:SQL question . . .
« Reply #3 on: January 20, 2005, 10:07:59 AM »
|
|
The goal is, get a total teacher count for each prefecture.
|
|
Logged
|
"how does this work? where do my teeth go?" - a lepord cub trying to eat a turtle
-- panda cam
|
|
|
Ryo-Ohki
[Wumpa]
Board Admin
Karma: +30/-6
Offline
Posts: 187
|
|
Re:SQL question . . .
« Reply #4 on: January 20, 2005, 10:08:46 AM »
|
|
I hate joins too, but they're a rather necessary evil.
|
|
Logged
|
"how does this work? where do my teeth go?" - a lepord cub trying to eat a turtle
-- panda cam
|
|
|
Ryo-Ohki
[Wumpa]
Board Admin
Karma: +30/-6
Offline
Posts: 187
|
|
Re:SQL question . . .
« Reply #5 on: January 20, 2005, 10:12:20 AM »
|
|
I guess I should add, the query is fast enough. I just hate having either filesort or a temp being used if I can avoid it.
|
|
Logged
|
"how does this work? where do my teeth go?" - a lepord cub trying to eat a turtle
-- panda cam
|
|
|
Ryo-Ohki
[Wumpa]
Board Admin
Karma: +30/-6
Offline
Posts: 187
|
|
Re:SQL question . . .
« Reply #6 on: January 20, 2005, 10:29:42 AM »
|
|
Okay ignore this whole thread. I am asleep and figured out why this is happening, and I just want to crawl in a hole now . . . . . 
|
|
Logged
|
"how does this work? where do my teeth go?" - a lepord cub trying to eat a turtle
-- panda cam
|
|
|
Porter
[Wumpa]
Board Admin
Karma: +176/--88
Offline
Gender:
Posts: 3910
|
|
Re:SQL question . . .
« Reply #7 on: January 20, 2005, 12:01:24 PM »
|
|
Good, glad I could help. 
|
|
Logged
|
[Wumpa] Porter
--Silent, professional, lethal... sometimes.
|
|
|
|
Pages: [1]
|
|
|
|
|
|
CSReloaded Forums | Powered by YaBB SE
© 2001-2003, YaBB SE Dev Team. All Rights Reserved. |
|
|
