Slow query with title sort

[ianevans]

This query example is hurting my head and my server...taking just over three seconds.

I'm pretty sure I have the wrong indices or type of indices...or is it the sort_title that's the issue.

Thanks for any insight.

EXPLAIN SELECT poster_data . * , IF( aptitle LIKE 'The %', SUBSTRING( aptitle, 5 ) , IF( aptitle LIKE 'A %', SUBSTRING( aptitle, 3 ) , IF( aptitle LIKE 'An %', SUBSTRING( aptitle, 4 ) , aptitle ) ) ) AS sort_title
FROM poster_data
WHERE aptype LIKE '%Poster%'
GROUP BY sort_title ASC
HAVING sort_title LIKE '5%'
ORDER BY sort_title ASC
LIMIT 0 , 32;

Table: poster_data
Type: All
Possible_keys: Null
Key: Null
Key_len: Null
Ref: Null
Rows: 61619
Extra: Using where; Using temporary; Using filesort

posterid UNIQUE 61619
aptitle INDEX 30809
aptype INDEX 27
aptype_2 FULLTEXT 28

[phranque]

you probably need a multi-column index on (aptype, aptitle) and you can probably get away with a partial column index on aptitle if your version of your db allows it.

that "Using filesort" is probably what's killing you.

try the same explain/query without the where clause and see if it uses an index and works more efficiently and then go from there.