En principio, te sugiero que publiques tu pregunta en el foro de MySQL, en donde posiblemente te puedan dar una ayuda más precisa.
Ahora, respecto a lo que planteas, hay algunas cosas que te sugeriría revisaras:
1. Si deseas usar la memoria RAM en tu servidor para gestionar tablas temporales en MySQL, verifica si existe en tu máquina algún punto de montaje que opere sobre memoria (en sistemas unix usualmente se reportan como particiones tipo 'tmpfs'). Luego modifica tu configuración de MySQL para usar ese sistema de archivos.
Por ejemplo, si sabes que /var/tmp en tu máquina es de tipo
tmpfs, edita my.cnf y usa esa ruta en el parámetro tmpdir:
2. Revisa tu consulta MySQL, y si te es posible, replantea la operación que estás haciendo. Esto puede tomar un poco de trabajo, pero si realmente es necesario en vista de las limitaciones que estás alcanzando, no hay otra manera.
Por ejemplo:
Código:
LEFT JOIN fanfiction_authorprefs AS ap
ON author.uid = ap.uid
¿Es este JOIN realmente necesario? Ya que la consulta no utiliza 'ap' para nada más, quizás puedas prescindir de esta parte de la consulta, depende de cómo trabaje tu sistema.
También:
Código:
FIND_IN_SET(author.uid, stories.coauthors)
Me parece un poco extraño usar un campo tipo SET para almacenar IDs de autores, que supongo pueden ser valores en un rango bien amplio. Probablemente haya una forma más eficiente (en espacio y tiempo de consulta) para hacer lo mismo que esa expresión FIND_IN_SET está haciendo actualmente.
3. Ejecuta
EXPLAIN SELECT argumentos de tu consulta. Esto suele darte una idea más clara de qué tipo de operaciones hace MySQL para devolverte tus resultados. Si no encuentras mucho sentido en la información que recibes, pregunta a personas más familiarizadas con esto (el foro de MySQL puede ser un buen lugar :).
4. Al optimizar consultas SELECT a veces resulta útil convertir JOINs explícitos en declaraciones WHERE tradicionales. Por ejemplo, prueba cambiando tu consulta a algo como:
Código:
SELECT count( stories.sid ) as stories,
author.penname as penname,
author.uid as uid
FROM fanfiction_authors as author,
fanfiction_authorprefs AS ap,
fanfiction_stories AS stories
WHERE author.penname LIKE '%Ejemplo%'
AND author.uid = ap.uid
AND stories.validated > 0
AND (FIND_IN_SET(author.uid, stories.coauthors) > 0
OR stories.uid = author.uid)
GROUP BY author.uid
Mira si el tiempo de ejecución cambia significativamente. También mira qué cambios observas usando EXPLAIN en esta nueva consulta, en comparación con la consulta original.