Re: A never-ending query (almost)
In article <46jkpg$49l@xmission.xmission.com>
dirk@xmission.xmission.com "Derrek Poulson" writes:
<snip>
> A query of the form:
>
> select * from table where field1="xxx">
> comes back within a second or so. A query like:
>
> select * from table where field1="xxx" and field2="yyy">
> takes between <1 second to over an hour (!),
> depending on the value of field1.
>
The answers about checking the distribution of 'field1' and running
'UPDATE STATISTICS' look useful, but you don't mention if there is
any 'order by' clause. I have found the following will stop indexes
being used!
SELECT * FROM table
WHERE field1 = "xxx"
AND field2 = "yyy"
ORDER BY field3
whereas
...
ORDER BY field1, field2, field3
will use the index mentioned on field1, field2, field3, ...
If this is the problem 'set explain on' will show it clearly.
Fragmentation?
--
============================================================================
Sally Woolrich | This mail contains my personal
sally@excelsis.demon.co.uk | views not those of my employer!
============================================================================