Preventing filesort in a simple (but big) query?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Preventing filesort in a simple (but big) query?

    I think I'm missing something. Any help would be appreciated.

    I've got a large database (2+M records) with about 40 fields. It's doing very enhanced log analysis. One of the fields is the title of an article, defined as a VARCHAR(255). The field is indexed. The engine is InnoDB. I have a logID field as the primary key.

    When I a very simple query, the process takes forever:

    SELECT articleTitle, count(*) from logdata group by articleTitle order by count(*) desc

    Explain says it's using filesort, which I know is bad, but I'm not sure how to prevent that. Here's the explain:

    *************************** 1. row ************************
    id: 1
    select_type: SIMPLE
    table: logdata
    type: index
    possible_keys: NULL
    key: articleTitle_index
    key_len: 258
    ref: NULL
    rows: 1955105
    Extra: Using index; Using temporary; Using filesort

    Sadly, I don't have an articleID that's shorter because this is derived data. And I have a bunch of other related queries, like a list of the most popular authors, that also takes forever (we're talking an hour or more).

    So, what can I do to make this (and all the searches like it) fast enough to be useful?

    Thanks in advance!