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!