CODEWITHSUNDEEP
phpmysqlsqlexec
Kuwame Brown
Kuwame Brown

Reputation: 533

Slow Sql Execution

I have a table that contains at least 60,000 rows. My query is just basic, and it is something like this:

SELECT `table`.name, `table`.age, `table`.points
                              FROM table
                              GROUP BY name, age
                              ORDER BY date
                              DESC
                              LIMIT 12

The result is like (12 total, Query took 1.2211 sec), sometimes it even takes 2 seconds to just return 12 rows.

What should be done to make my query faster?

EXPLAIN QUERY:

id | select_type | table |  type  | possible_keys |  key  |  key_len  |  ref  | rows  | Extra |
1       Extra      table    ALL        NULL          NULL    NULL        NULL   65704   Using temporary; Using filesort

Upvotes: 0

Views: 66

Answers (2)

Mihai
Mihai

Reputation: 26784

This is too long for a comment. An index on date will help,without it it is forced to do a table scan. Also what is the cardinality of name,that is the number of distinct names divided on the number of rows ?In case it is low you can create a table with those distinct names and JOIN on that,aiming for a loose index scan. For beginning try a composed index :

 ALTER TABLE t1 ADD KEY indexName (name,age,date)

For more details put an EXPLAIN before your query and edit your question with the results.

Upvotes: 1

Steel Brain
Steel Brain

Reputation: 4461

You might want to create indexes in your SQL Database. If you're using MYSQL you can create them like this

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

For your case it would be like:

ALTER TABLE `table` ADD INDEX (name);
ALTER TABLE `table` ADD INDEX (age);
ALTER TABLE `table` ADD INDEX (points);

You might also want to have a look at this question

Upvotes: 0

Related Questions