How to speed up AVG(), MIN(), MAX() functions

tedd · #1 (**permalink**) 12-17-10, 07:19

Hi!

I have a table with about 400k rows.
When I query this:

Code:

SELECT MIN(some_metrics) FROM big_table

everything is OK, execution time is about 0.02 sec.

But when I add a WHERE clause

Code:

SELECT MIN(some_metrics) FROM big_table WHERE parent_id = 10

this query executes in about 2 minutes. Indexes on parent_id and some_metrics columns are created.

The question is - how to speed up this query?

tedd · #2 (**permalink**) 12-17-10, 07:39

Just read some more manuals.. I guess I should create a composite index (parent_id, some_metrics), right?

r937 · #3 (**permalink**) 12-17-10, 08:40

Quote:

Originally Posted by tedd

I guess I should create a composite index (parent_id, some_metrics), right?

right

for that particular query, that compound index will be a covering index

tedd · #4 (**permalink**) 12-17-10, 10:09

Thanks for the answer!

I'm still confused with all this index stuff

Would you suggest something to read?

r937 · #5 (**permalink**) 12-17-10, 10:41

Quote:

Originally Posted by tedd

Would you suggest something to read?

tedd · #6 (**permalink**) 12-20-10, 07:39

Thanks, nice article! Got some questions answered.