Help needed with slow query

g_chime@yahoo.com wrote:
> Question 1: Is it ok to have to indexes one by f1 and another by f1 or
> should I have one index by (f1, f2)?

It's okay to define indexes either way, but MySQL will use only one
index per table in a given query. So in your case, I'd recommend trying
the compound index and see if it makes a difference to performance.

http://dev.mysql.com/doc/refman/5.0/...l-indexes.html says:

"If a multiple-column index exists on col1 and col2, the appropriate
rows can be fetched directly. If separate single-column indexes exist on
col1 and col2, the optimizer tries to find the most restrictive index by
deciding which index finds fewer rows and using that index to fetch the
rows."

In other words, if you have two separate indexes, one on f1 and one on
f2, the doc appears to say that it can only use one index or the other,
but will try to make the best decision about which of the two to use.

Verify which index the query is using on your given query using EXPLAIN.
Usage of EXPLAIN and interpretation of its output is documented here:
http://dev.mysql.com/doc/refman/5.0/en/explain.html

> Question 2: How can I speed it up?

Make sure you do proper index maintenance, with OPTIMIZE TABLE.
Periodic maintenance of indexes is important to make sure they keep
functioning efficiently.

See:
http://dev.mysql.com/doc/refman/5.0/...ize-table.html
http://dev.mysql.com/doc/refman/5.0/...hk-syntax.html

This may be especially important if your table has a lot of activity,
both new insertions and deletions of old records. Based on the values
you mentioned in your search criteria, I'm going to guess that you are
working with scientific measurements of some type, and that you do have
a lot of activity in this table.

Regards,
Bill K.

>I have a large table (~200 columns, ~10 million rows) and I am trying
>to query by two float rows, say f1 and f2:
>
>
>SELECT... FROM large_table WHERE f1 BETWEEN 10.82 AND 113.998 AND f2
>BETWEEN -124.99 AND 23.5;
>
>
>The query is extremely slow; it takes hours.

What indexes do you have now? None?

>Question 1: Is it ok to have to indexes one by f1 and another by f1 or
>should I have one index by (f1, f2)?

Having two single indexes on the same variable is pointless.
If you have two separate indexes, one on f1 and one on f2, MySQL
might be able to use the one with the fewest records in the range.

An index on (f1, f2) is no better than (and probably worse than) an
index on f1 alone. It would be usable for an exact match on f1 and
a range on f2.

What other queries use this table?

>Question 2: How can I speed it up?

Delete most of the records?

Gordon L. Burditt