Cache / Reutilize uma Subconsulta no MySQL
Eu tenho uma consulta MySQL muito complexa que inclui o uso da mesma subconsulta três vezes. O MySQL realmente executará a subconsulta três vezes? (É caro.) Nesse caso, existe uma maneira de eu dizer ao MySQL para salvar ou armazenar em cache os resultados para que isso não ocorra? Eu poderia salvar os dados em uma grande matriz e depois realimentá-los para o MySQL, mas prefiro não movê-los e voltar ao banco de dados dessa maneir
Esta é a subconsulta que aparece três vezes:
SELECT id FROM programs
WHERE submitter_id=32 AND id in (
SELECT id FROM programs
WHERE feed_id=2478 AND id in (
SELECT program_id FROM playlist_program_map
WHERE playlist_id=181)))
E aqui está um exemplo da consulta completa na qual a consulta aparece:
SELECT object_id, programs.created AS created,
MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance
FROM comments_programs USE INDEX (text)
LEFT JOIN programs ON programs.id=object_id
WHERE object_id IN (
SELECT id FROM programs
WHERE 1 AND id IN (
SELECT id FROM programs
WHERE submitter_id=32 AND id in (
SELECT id FROM programs
WHERE feed_id=2478 AND id in (
SELECT program_id FROM playlist_program_map
WHERE playlist_id=181))))
AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)>0)
UNION (
SELECT object_id, programs.created AS created,
MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance
FROM descriptions_programs USE INDEX (text)
LEFT JOIN programs ON programs.id=object_id
WHERE object_id IN (
SELECT id FROM programs
WHERE 1 AND id IN (
SELECT id FROM programs
WHERE submitter_id=32 AND id in (
SELECT id FROM programs
WHERE feed_id=2478 AND id in (
SELECT program_id FROM playlist_program_map
WHERE playlist_id=181))))
AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)>0 AND current=1 )
UNION (
SELECT object_id, programs.created AS created,
MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE) AS relevance
FROM titles_programs USE INDEX (text)
LEFT JOIN programs ON programs.id=object_id
WHERE object_id IN (
SELECT id FROM programs
WHERE 1 AND id IN (
SELECT id FROM programs
WHERE submitter_id=32 AND id in (
SELECT id FROM programs
WHERE feed_id=2478 AND id in (
SELECT program_id FROM playlist_program_map
WHERE playlist_id=181))))
AND MATCH(text) AGAINST ('excellent ' IN BOOLEAN MODE)>0 AND current=1;