Re: Suitable index for the query
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- Date: Sun, 27 Jun 2010 11:01:08 -0400
Oh, yes, absolutely, Stephane.
Cary Millsap
Method R Corporation
http://method-r.com
http://carymillsap.blogspot.com
On Sun, Jun 27, 2010 at 5:33 AM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote:
> Cary,
>
> Although I agree with all you said, I have to nuance the following
> sentence, not really for you :-), but for those who read this thread:
> >
> > The bottom line: If you don't know what your queries look like, then
> > there's no way you can know what indexes will optimize your system.
> > Specifically, you /cannot/ define indexes optimally by looking only at
> > your data. You have to look at your SQL.
> >
>
> I'd rather say "look at what you want to do" rather than "look at your
> SQL".
>
> Firstly, SQL code, unless you get a canned application, isn't immutable.
> I keep function based indexes, for instance, for cases when there is
> really no other way to salvage a query - I fisrt try to change
>
> where substr(my_column, 1, 5) = 'XXXXX'
>
> into
>
> where my_column like 'XXXXX%'
>
> if my_column is indexed and if the index is likely to be useful. To
> summarize, don't assume that statements are sacred, even if you are a
> production DBA. Don't hesitate to discuss them with developers, you may
> teach them something (or make them exclaim "oops!")
>
> Secondly, I have met many applications where individual queries were
> untunable but yet there still was ample scope for improvement. Suppose
> that you get from a file a large number of values that you have to use
> as search criteria to query a table. If in your program you open the
> file, read record after record and use each value read as the search
> condition in a WHERE clause, you may decide that an index on the column
> that contains this value would speed up the process. On the other hand,
> if you create an external table atop the file and use it in a straight
> join instead of looping on a SELECT statement, you may discover that the
> index isn't really useful after all, and that a hash join far
> outperforms the loop, even with the index. It often requires a bit of
> lateral, "out of the statement" thinking, but it's often very rewarding.
>
> My 0.02 euros
>
> S Faroult
>
>
>
>
>
>
>
Other related posts:
