Help optimizing a query for MySQL

De openkb
Aller à : Navigation, rechercher

Sommaire

Questions

I have a MySQL query that goes as follows

SELECT
    count(`clicks`.`user_id`) as total,
    `users`.`fullname`
FROM
    `users`,
    `clicks`,
WHERE
    `users`.`id` = `clicks`.`user_id`
GROUP BY
    `clicks`.`user_id`
ORDER BY
    `total` desc
LIMIT
    0,20;

I am running statistics on several button pressing type games. It has a user table and a clicks table. It logs clicks from a specific user. The user can click the button whenever they want. Twenty clicks one day, thirty another, etc. They aren t all in a row.

Currently there are about ~180k clicks for ~2k users. This query takes 1.38 seconds to run on average. I would like to speed it up if that s at all possible.

Answers

    • Is USERS.id defined as the primary key for the table? It should be...
    • Assuming INNODB, does CLICKS.user_id have a foreign key constraint on it to associate its values with USERS.id?
    • USERS.id and CLICKS.user_id are a numeric data type (IE: INT), not text based?
    • Indexes should be added (if they don t already exist) for:
      • CLICKS.user_id
      • USERS.fullname

    http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html

    ANALYZE TABLE USERS;
    ANALYZE TABLE CLICKS;
    

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/3072650/help-optimizing-a-query-for-mysql

Related

Outils personnels
Espaces de noms

Variantes
Actions
Navigation
Outils