On Fri, 2002-10-11 at 16:08, Chris Stoughton wrote:
> I sent a similar question a few days ago. I don't think there was a
> response. If there was, sorry that I missed it. I have worked around
> the issue, but would like to know whether there is something I can do to
> improve the orignal query.
>
> I have a table with two spatial indices -- ra and decl, for right
> ascension and declination , think of them as x,y coordinates. In order
> to match objects in one table to a second table, I choose a set of
> objects in the first table, find the limits of ra,decl, and then query
> the second table based on these limits. I then do matching in a
> separate program, between these two lists.
>
> For a specific example of one pair of queries:
>
> select ra,decl from firstTable where fieldId=1
> (based on the results of this query, calculate raMin,raMac, declMin, and
> declMax -- 1.1, 1.2, 3.4, 3.5 in this example)
> select ra,decl,a,b,c,d from secondTable where ra between 1.1 1.2 and
> decl between 3.4 3.5
>
Hi,
You could try a combined index on ra and decl, eg.
create index c1 on secondTable (ra, decl );
What is probably happening now is that only one of your
indexes is being used in the query (either ra or decl)
and then every record falling in that range is read to
find those matching the second clause. The amount of
work involved in that depends on the distribution of the values.
Try running explain on your query and see what indexes are
being used, eg:
explain select ra,decl,a,b,c,d from secondTable
where ra between 1.2 1.3 and
decl between 4.0 4.1
Then try adding a combined index and see if explain has
changed its mind.
==
Martin
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
