Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    25

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

    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?

  2. #2
    Join Date
    Nov 2010
    Posts
    25
    Just read some more manuals.. I guess I should create a composite index (parent_id, some_metrics), right?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tedd View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2010
    Posts
    25
    Thanks for the answer!

    I'm still confused with all this index stuff Would you suggest something to read?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tedd View Post
    Would you suggest something to read?
    start here: Optimizing your MySQL Application
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2010
    Posts
    25
    Thanks, nice article! Got some questions answered.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •