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

**URL:** <https://forums.percona.com/t/preventing-filesort-in-a-simple-but-big-query/347>\
**Category:** Other MySQL® Questions\
**Created:** [June 5, 2007, 3:15pm UTC](https://forums.percona.com/t/preventing-filesort-in-a-simple-but-big-query/347 "2007-06-05T15:15:25Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Raegis](https://avatars.discourse-cdn.com/v4/letter/r/d78d45/32.png) [@Raegis](https://forums.percona.com/u/Raegis)\
**Post date:** [June 5, 2007, 3:15pm UTC](https://forums.percona.com/t/preventing-filesort-in-a-simple-but-big-query/347/1 "2007-06-05T15:15:25Z")

</div>

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!