I have a Pylons application using SQLAlchemy to interact with a SQLite
db. When one of my searches results in an OUTER LEFT JOIN query, the
system takes a VERY long time to return results (i.e., several
minutes). The puzzling thing is that if I run an equivalent query at
the SQLite command line there is no problem. Also, when the RDBMS is
MySQL the query does not take a long time.
Here are the schemas of the relevant tables (form and gloss).
CREATE TABLE form (
id INTEGER NOT NULL,
transcription VARCHAR(255) NOT NULL,
"phoneticTranscription" VARCHAR(255),
"morphemeBreak" VARCHAR(255),
"morphemeGloss" VARCHAR(255),
comments TEXT,
"speakerComments" TEXT,
grammaticality VARCHAR(255),
"dateElicited" DATE,
"datetimeEntered" TIMESTAMP,
"datetimeModified" TIMESTAMP,
"syntacticCategoryString" VARCHAR(255),
"morphemeBreakIDs" VARCHAR(1023),
"morphemeGlossIDs" VARCHAR(1023),
elicitor_id INTEGER,
enterer_id INTEGER,
verifier_id INTEGER,
speaker_id INTEGER,
elicitationmethod_id INTEGER,
syntacticcategory_id INTEGER,
source_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(elicitationmethod_id) REFERENCES elicitationmethod
(id),
FOREIGN KEY(verifier_id) REFERENCES user (id),
FOREIGN KEY(elicitor_id) REFERENCES user (id),
FOREIGN KEY(speaker_id) REFERENCES speaker (id),
FOREIGN KEY(syntacticcategory_id) REFERENCES syntacticcategory
(id),
FOREIGN KEY(source_id) REFERENCES source (id),
FOREIGN KEY(enterer_id) REFERENCES user (id)
);
CREATE TABLE gloss (
id INTEGER NOT NULL,
gloss TEXT NOT NULL,
"glossGrammaticality" VARCHAR(255),
form_id INTEGER,
"datetimeModified" TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY(form_id) REFERENCES form (id)
);
I'm using the SQLAlchemy ORM. When I print the relevant query object,
I get the following. (This is the query that takes forever.)
SELECT form.id AS form_id, form.transcription AS form_transcription,
form."phoneticTranscription" AS "form_phoneticTranscription",
form."morphemeBreak" AS "form_morphemeBreak", form."morphemeGloss" AS
"form_morphemeGloss", form.comments AS form_comments,
form."speakerComments" AS "form_speakerComments", form.grammaticality
AS form_grammaticality, form."dateElicited" AS "form_dateElicited",
form."datetimeEntered" AS "form_datetimeEntered",
form."datetimeModified" AS "form_datetimeModified",
form."syntacticCategoryString" AS "form_syntacticCategoryString",
form."morphemeBreakIDs" AS "form_morphemeBreakIDs",
form."morphemeGlossIDs" AS "form_morphemeGlossIDs", form.elicitor_id
AS form_elicitor_id, form.enterer_id AS form_enterer_id,
form.verifier_id AS form_verifier_id, form.speaker_id AS
form_speaker_id, form.elicitationmethod_id AS
form_elicitationmethod_id, form.syntacticcategory_id AS
form_syntacticcategory_id, form.source_id AS form_source_id
FROM form LEFT OUTER JOIN gloss ON form.id = gloss.form_id
WHERE gloss.gloss LIKE :gloss_1 ORDER BY form.id DESC
However, when I run the following at the SQLite prompt, the results
are returned immediately.
SELECT * FROM form LEFT OUTER JOIN gloss ON form.id = gloss.form_id
WHERE gloss.gloss LIKE '%dog%' ORDER BY form.id DESC;
Am I missing something obvious? I appreciate any help. Thanks
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
