On Mon, Jul 5, 2010 at 11:19 PM, Simon Litchfield <si...@slicmedia.com> wrote:
>> I would like to know how you're validating your assertion that MySQL
>> is the most used backend. It doesn't match my experience or
>> observation.
>
> Nobody knows for sure. I'd put my money on it though.
>
>> The fact that this is a MySQL-specific issue is perhaps the biggest
>> impediment to my enthusiasm about this specific feature (as proposed).
>> I've spent enough time in the past covering for the inadequacies of
>> MySQL. I don't particularly relish the thought of adding more time to
>> that particular ledger. :-)
>
> I know, I've seen you pulling your hair out :-)
>
>> Having an implementation ignore kwargs is the obvious approach to
>> providing this feature cross platform. The issue for me is what
>> exactly the kwargs should be, and how they would be interpreted. It
>> isn't clear to be how 'index="my_index"' maps to the application of
>> USE INDEX, IGNORE INDEX or FORCE INDEX in a query, or how the
>> dictionary syntax would map to the situation where you have two
>> appearances of a given table in a query.
>
> Great, so lets define the syntax then and away we go.
>
> 1) I'd say index= would map to MySQL's USE INDEX and Oracle's index().
> The others you mention there would be MySQL-specific kwargs. Or, more
> simply, ignore and force could be '-index' and '+index'. Bit like we
> use + and - for order_by.
The problem with sharing a common keyword is that an index hint
specified for Oracle won't be useful (or even legal, necessarily) for
MySQL. If I'm going to ship my reusable app, and I know that one
particular query will perform badly under MySQL, it would be good to
be able to ship with whatever hinting strategy will help. However,
that same hint may not be appropriate for other backends, even if
those backends do support hinting.
Effectively, there's a namespace issue here; we need to be able to
specify "MySQL" hints independently of "Oracle" hints. As a further
complication -- as of Django 1.2, database backends are a fully
qualified path, so "mysql" isn't necessarily sufficient to identify a
backend (although I'm slightly more comfortable putting this sort of
collision in the category of "acceptable risk" if there's no obvious
way to avoid it).
As for the "+/-" syntax; I'm not wild about it. I'm not an Oracle
expert, but from a quick inspection it appears that there are dozens
of optimizations that could potentially be applied, and none of them
fall into the "USE/FORCE/IGNORE" triptych (many of them don't even
apply to indexes directly). Even on MySQL -- optimizations like "USE
INDEX x FOR ORDER BY" won't be covered by a simple syntax.
I'm not expecting that you will provide a patch that will implement
all possible hints, but I don't want to adopt a syntax that will cut
off future options. I want to have some confidence that it would be
*possible* to implement Oracle hinting or MySQL FOR ORDER BY hinting
if they were so inclined.
> 2) The multiple table issue is edge case but worth supporting if it
> doesn't complicate the syntax too much. Rather than use models as keys
> we could use the filter/exclude name; eg {'relatedmodel__field':
> 'index'} would apply 'index' specifically to the 'relatedmodel__field'
> join.
Using the filter/exclude name sounds interesting, but skips the first
step of putting an index hint on the origin table. For example,
Book.objects.filter(author__name='foo') -- now put an index hint on
Book; now put one on Author. If Author and Book are an a m2m relation
-- put a hint on the m2m table.
I'd need to see a more complete set of worked examples to see how this
would pan out.
Yours,
Russ Magee %-)
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-develop...@googlegroups.com.
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en.
