Slow Sql Execution

Question

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

If you've got performance problems, let MySQL [EXPLAIN](http://dev.mysql.com/doc/refman/5.6/en/explain.html) to you what work got to be done. And as @SteelBrain pointed out: Indexes are great to improve performance in many cases. Please add the output of EXPLAIN and the CREATE TABLE statement of your table. — VMai, Sep 20 '14 at 18:24
And remove your ORDER BY, it makes no sense at all with this statement. — VMai, Sep 20 '14 at 18:25
Please consider that the value in the column `points` is not the newest one per group. It is an indeterminate one. Please read [MySQL Extensions to GROUP BY](http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html). If you can't use an aggregate function on points then you could use a subselect or a left join. — VMai, Sep 20 '14 at 18:32
Missing an aggregate function in the query. Note that if you have 60.000 records in the table but get only 12 records in the result doesn't mean that there's no full table scan. And an index won't change that. — Gervs, Sep 20 '14 at 18:33
I remove the GROUP BY, it is still taking too much time to return the data — Kuwame Brown, Sep 20 '14 at 18:33
@Gervs There's the LIMIT clause that causes the result of 12 rows. — VMai, Sep 20 '14 at 18:37
@VMai: Yes I know, but grouping is done afterwards, so all rows have to be scanned before the LIMIT can be applied. — Gervs, Sep 20 '14 at 18:42
@Gervs An index on (name, age) should boost the performance of the GROUP BY operation nevertheless. — VMai, Sep 20 '14 at 18:44
Whether I add or remove the GROUP BY, I will get the same results. It is still slow. — Kuwame Brown, Sep 20 '14 at 18:46
@KuwameBrown: We wait for you to post the output of EXPLAIN and whether there are any indexex, so please add the output of SHOW CREATE TABLE too. — VMai, Sep 20 '14 at 18:47
You have no indexes used,run the statement in my answer and run the query again. — Mihai, Sep 20 '14 at 19:24

score 1 · Answer 1 · answered Sep 20 '14 at 18:24

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.

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

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

edited May 23 '17 at 10:33

Community

1
1

answered Sep 20 '14 at 18:19

Steel Brain

4,321
28
38

A combined index on (name, age) would probably be a good idea. – VMai Sep 20 '14 at 18:21
will the combined index `name and age` work if the user searches only by the name or only by the age? – Steel Brain Sep 20 '14 at 18:25
It will work if searched by name only, but not by the age only. Here we have the combination of both columns, so it could help. – VMai Sep 20 '14 at 18:28

Slow Sql Execution

2 Answers2