Thanks for the suggestion Philip. However, if I run
sqlite> 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 '%dog%' ORDER BY form.id DESC; The results are returned instantly. The above is the *exact* query generated by SQLAlchemy, except that I've replaced ":gloss_1" with "'%dog%'", which (I'm assuming) is what SQLAlchemy itself does. So, does this mean that it's not SQLite's fault? Or am I not really running the same query that SQLAlchemy generates for SQLite? Joel On Nov 4, 1:42 pm, Philip Jenvey <[email protected]> wrote: > On Nov 4, 2011, at 12:48 PM, Joel wrote: > > > 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. > > This sounds like SQLite's fault -- you can confirm that by running the > *exact* query via the command line tool as well. > > -- > Philip Jenvey -- 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.
