MySql - Como posso acelerar esta consulta

Aqui está sua consulta original:

SELECT
    user_actions.*,
    users.twitter_username,
    users.email
FROM
    `user_actions`  
    INNER JOIN users
    ON (user_actions.user_id=users.id)
    ORDER BY timestamp
    DESC LIMIT 0, 30
;

A primeira coisa que noto é que você está juntando duas tabelas inteiras. Como você só precisa de twitter_usernamee emailda userstabela, você só deve ingressar usersusando três colunas id: twitter_usernamee email.

A segunda coisa é a LIMITcláusula. Ele está sendo executado após a junção. Você deve executá-lo antes da junção. No seu caso, você está solicitando as 30 ações de usuário mais recentes. Se você puder garantir que apenas 30 linhas sejam recuperadas de user_actions, a junção deverá operar muito mais rapidamente.

Se você ler a resposta de @DTest , seus dois primeiros marcadores já informam o que há de errado na consulta por causa das ações que o mysql executará na coleta de dados de cada tabela. A chave é entender como serão as tabelas temporárias enquanto a consulta está sendo processada e onde os dados residirão (memória ou disco).

O que você precisa fazer é refatorar a consulta para enganar o MySQL Query Optimizer. Force a consulta a produzir tabelas temporárias menores. Na maioria dos casos, as alterações de configuração em my.cnf devem fazer uma diferença dramática. Em outros casos, como este, refatorar a consulta pode ser suficiente.

Aqui está minha proposta de mudança para sua consulta que deve funcionar mais rápido:

SELECT
    ua.*,
    u.twitter_username,
    u.email
FROM
    (SELECT * FROM `user_actions`
    ORDER BY timestamp DESC LIMIT 30) ua
    LEFT JOIN
    (SELECT id,twitter_username,email FROM `users`) u
    ON (ua.user_id=u.id)
;

Aqui estão os motivos para refatorar a consulta:

MOTIVO Nº 1

Se você olhar a tabela embutida ua, eu recupero apenas 30 linhas usando LIMIT. Isso acontecerá não importa o tamanho da user_actionsmesa . Já está ordenado porque o ORDER BY timestamp DESCacontece antes do LIMIT.

MOTIVO Nº 2

Se você olhar a tabela inline , uela tem id,,, . O é necessário para implementar a junção.twitter_usernameemailid

MOTIVO Nº 3

Eu uso LEFT JOINem vez de INNER JOINpor dois (2) motivos:

1. Preservar a ordem da consulta com base emua
2. Exiba todas as ações do usuário caso o user_id no uanão exista mais nas userstabelas.

Fazer essas coisas forçará as tabelas temporárias a serem menores. No entanto, você ainda precisará implementar o marcador nº 3 da resposta do @DTest para evitar que as tabelas temporárias cheguem ao disco.

Bem, o principal problema é que, como sua consulta não possui nenhuma filtragem (sem WHEREinstrução), ela coloca todas as linhas com colunas user_actions.*, twitter_username, emailem uma tabela temporária para fazer a classificação.

Então, a primeira coisa que eu faria é tentar limitar o número de linhas que vão para o seu conjunto de resultados. Por exemplo, eu diria adicionar um WHERE timestamp > DATE_SUB(NOW(), INTERVAL 7 DAY)para obter apenas resultados nos últimos 7 dias (se isso for aceitável para o seu caso de uso).

Em seguida, eu alteraria a consulta para obter apenas as colunas necessárias user_actionspara reduzir a quantidade de informações necessárias para colocar em uma tabela temporária.

Agora que você pode ou não ter removido linhas/colunas que precisam ser colocadas na tabela temporária para serem classificadas, vamos ver como o MySQL lida com tabelas temporárias. Da documentação sobre a tmp_table_sizevariável (ênfase adicionada):

O tamanho máximo de tabelas temporárias internas na memória. (O limite real é determinado como o mínimo de tmp_table_size e max_heap_table_size.) ¹ Se uma tabela temporária na memória exceder o limite, o MySQL automaticamente a converterá em uma tabela MyISAM em disco.

Primeiro, deixe-me apontar a ressalva representada pelo sobrescrito ¹ : O tamanho da tabela temporária criada na memória é o mínimo de tmp_table_sizeou max_heap_table_size, portanto, se você aumentar um, certifique-se de aumentar o outro.

Se a quantidade de seus dados exceder o tamanho mínimo dessas duas variáveis, eles serão colocados no disco. O disco está lento. Não faça disco se puder evitá-lo!

Para recapitular:

• Limite a quantidade de linhas que você está classificando, usando WHERE. Mesmo que você esteja fazendo um LIMIT, todas as linhas ainda estão sendo colocadas na tabela temporária para classificação.

• Limite o número de colunas que você está solicitando. Se você não precisa deles, não os peça.

• Último recurso, aumente o tamanho de tmp_table_sizee max_heap_table_sizese a consulta estiver aumentando sua Created_tmp_disk_tablesvariável de status. Além disso, não aumente isso drasticamente. Pode ter impacto no desempenho, dependendo do seu hardware e da quantidade de RAM que você possui no seu servidor.