CODEWITHSUNDEEP
mysqloptimizationquery-optimization
Jim Goodall
Jim Goodall

Reputation: 165

MySql query optimization suggestions

I have a very simple table and a query I would like to know if can be optimized in some way? Is there something better to use than the EXPLAIN statement when looking for a query optimization?

It only has 1500 rows now, but it's intented to have a lot more (probably above 500,000 or 1,000,000 rows).

Query:

SELECT id, SUM( ABS(weight)) AS sumWeight FROM myTable GROUP BY id ORDER BY sumWeight ASC LIMIT 1

Table structre

Field    Type           Null        Key    Default   Extra
id       mediumint(8)   unsigned    NO     PRI       NULL    
otherId  bigint(20)     unsigned    NO     PRI       NULL    
weight   smallint(6)    NO          NULL

When I use EXPLAIN to see what's going on it displays:

select_type   table   type  possible_keys   key         key_len ref     rows    Extra
SIMPLE        myTable index NULL            PRIMARY     11      NULL    1573    Using temporary; Using filesort

Upvotes: 0

Views: 79

Answers (1)

Anurag Arya
Anurag Arya

Reputation: 87

  • Make sure that you have created indexes on the columns which you are sorting and grouping
  • You can use Aqua Data Studio and generate an explain plan from there. It shows more details like CPU time, disk IO usage, etc. That would further help you understanding what is taking how much time and where you should focus to optimize further.

Upvotes: 1

Related Questions