how to optimize this select query? (MySQL)

Question

I've been working on a mysql database, and one of the most important tables that have the DataBase will be between 900 and 1000 million records. Im able to do this query:

select MAX(SEN_ID) as SEN_ID from senal group by variable_VAR_ID

but it takes like 12 minutes. How can I optimize this query?

This query was working fine when the database was small, but now that there are millions of rows in the database, I am realizing I should have looked at optimizing this earlier.

The query execute within EXPLAIN show this:

id select_type table type  possible_keys key                key_len ref  rows     Extra
1  SIMPLE      senal index NULL          fk_senal_variable1 4       NULL 6333242  Using index

zerkms · Accepted Answer · 2012-08-15 22:29:26Z

1

You need to create composite index variable_VAR_ID + SEN_ID

Note 1: not two separated indexes, but one composite
Note 2: yes, the order matters

PS: here is a syntax description of how to create indexes: http://dev.mysql.com/doc/refman/5.5/en/create-index.html

edited Aug 15, 2012 at 22:29

answered Aug 15, 2012 at 22:16

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jocelyn Over a year ago

Can you explain more, so your answer becomes more useful and we learn something? Why does the order matter? Why a composite index, and not 2 separate indexes?

patsweet Over a year ago

@Jocelyn The order matters because MySQL uses the leftmost field of a composite index first. So, in this instance, you'd want mysql to narrow down the variable_var_id to only the unique ones, then look at the SEN_ID to get the max for each unique variable_var_id. Check out this article on optimizing queries.link

user1601753 Over a year ago

So, after few hours of work, I was able to do this change: I'd created two index: INDEX SEN_SUPINDX` (SEN_ID DESC, SEN_FECH DESC, variable_VAR_ID DESC)and INDEX VAR_ID (variable_VAR_ID DESC)`but I dont know how to use it correctly.

user1601753 Over a year ago

table description: SEN_ID int(11), variable_VAR_ID int(10), SEN_FECH datetime, SEN_VALOR varchar(45)

zerkms Over a year ago

@user1601753: please read "note 1". You need to have 1 composite index, not 2 separated. PS: why did you create indexes with DESC?!

Collectives™ on Stack Overflow

how to optimize this select query? (MySQL)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related