How to optimize this query

This query is KILLING my website:

SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, keywords, votes, pic_rating, title, caption,hits,owner_id,owner_name from cpg1411_pictures WHERE ((aid='13'  ) )   ORDER BY title DESC  LIMIT 0 ,12

Code (markup):

Here is EXPLAIN:

ID = 1
select_type = SIMPLE
table = cpg1411_pictures
type = ref
possible_keys = aid_approved, pic_aid
key = aid_approved
key_len = 4
ref = const
rows = 11507
extra = using where, using filesort

This table is used for storing information for retrieval


Due to the large number of rows, where would you recommend adding the index?

 

aid + title

 

add index to AID. It depends, if aid = '13' constitutes more than 25% (approx.) of the table data, then it is better not to add index.

Add ordered index to TITLE OR on a regular basis re-insert the table by ordering by TITLE. i.e. create table temp as SELECT * from cpg1411_pictures. THEn replace the records with data in temp. this will save you some time in ordering the data because ORDER BY is resource hog...