Sfrgttk

label a part of commutative diagram

Symbolism of 18 Journeyers

Do I need an EFI partition for each 18.04 ubuntu I have on my HD?

Should I be concerned about student access to a test bank?

UK Tourist Visa- Enquiry

Error in master's thesis, I do not know what to do

Would mining huge amounts of resources on the Moon change its orbit?

Print a physical multiplication table

Hot air balloons as primitive bombers

Animating wave motion in water

Isn't the word "experience" wrongly used in this context?

CLI: Get information Ubuntu releases

How can an organ that provides biological immortality be unable to regenerate?

How to find the largest number(s) in a list of elements, possibly non-unique?

Knife as defense against stray dogs

What kind of footwear is suitable for walking in micro gravity environment?

Unfrosted light bulb

Imaginary part of expression too difficult to calculate

Have the tides ever turned twice on any open problem?

Do people actually use the word "kaputt" in conversation?

What is the reasoning behind standardization (dividing by standard deviation)?

How can I create URL shortcuts/redirects for task/diff IDs in Phabricator?

Why doesn't the chatan sign the ketubah?

What (if any) is the reason to buy in small local stores?

MySQL not using the correct index

0

I have following readings table, it has around 500 million rows.

CREATE TABLE readings (

   id int(11) NOT NULL AUTO_INCREMENT,

   value double NOT NULL,

   created_dt datetime NOT NULL,

   device_id int(11) NOT NULL,

   PRIMARY KEY (id),

   UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),

   KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id),

   CONSTRAINT readings_device_id_6a03c4ab761154d1_fk_device_id FOREIGN KEY (device_id) REFERENCES device (id)

) ENGINE=InnoDB AUTO_INCREMENT=138611438 DEFAULT CHARSET=latin1

It has an index on (created_dt, device_id) and an index on device_id( which is foreign key)

When i run following query

SELECT value, created_dt FROM readings WHERE (created_dt BETWEEN '2019-03-17 19:11:00' AND '2019-03-18 19:11:00' AND (device_id) IN (10, 11, 12));

Above query takes around 2 minutes.

DESCRIBE query returns the index used is 'readings_device_id_6a03c4ab761154d1_fk_device_id' which is foreign key index on device_id. However if i remove the above index and run the query again, it uses 'readings_created_dt_483d31a3654ede43_uniq' which is an index on both (created_dt, device_id) and retuns data in less than a second.

mysql

share

asked 1 min ago

AndroAndro

1

New contributor

Andro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I have following readings table, it has around 500 million rows.

CREATE TABLE readings (

   id int(11) NOT NULL AUTO_INCREMENT,

   value double NOT NULL,

   created_dt datetime NOT NULL,

   device_id int(11) NOT NULL,

   PRIMARY KEY (id),

   UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),

   KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id),

   CONSTRAINT readings_device_id_6a03c4ab761154d1_fk_device_id FOREIGN KEY (device_id) REFERENCES device (id)

) ENGINE=InnoDB AUTO_INCREMENT=138611438 DEFAULT CHARSET=latin1

It has an index on (created_dt, device_id) and an index on device_id( which is foreign key)

When i run following query

SELECT value, created_dt FROM readings WHERE (created_dt BETWEEN '2019-03-17 19:11:00' AND '2019-03-18 19:11:00' AND (device_id) IN (10, 11, 12));

Above query takes around 2 minutes.

DESCRIBE query returns the index used is 'readings_device_id_6a03c4ab761154d1_fk_device_id' which is foreign key index on device_id. However if i remove the above index and run the query again, it uses 'readings_created_dt_483d31a3654ede43_uniq' which is an index on both (created_dt, device_id) and retuns data in less than a second.

mysql

share

asked 1 min ago

AndroAndro

1

New contributor

Andro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I have following readings table, it has around 500 million rows.

CREATE TABLE readings (

   id int(11) NOT NULL AUTO_INCREMENT,

   value double NOT NULL,

   created_dt datetime NOT NULL,

   device_id int(11) NOT NULL,

   PRIMARY KEY (id),

   UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),

   KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id),

   CONSTRAINT readings_device_id_6a03c4ab761154d1_fk_device_id FOREIGN KEY (device_id) REFERENCES device (id)

) ENGINE=InnoDB AUTO_INCREMENT=138611438 DEFAULT CHARSET=latin1

It has an index on (created_dt, device_id) and an index on device_id( which is foreign key)

When i run following query

SELECT value, created_dt FROM readings WHERE (created_dt BETWEEN '2019-03-17 19:11:00' AND '2019-03-18 19:11:00' AND (device_id) IN (10, 11, 12));

Above query takes around 2 minutes.

DESCRIBE query returns the index used is 'readings_device_id_6a03c4ab761154d1_fk_device_id' which is foreign key index on device_id. However if i remove the above index and run the query again, it uses 'readings_created_dt_483d31a3654ede43_uniq' which is an index on both (created_dt, device_id) and retuns data in less than a second.

mysql

share

asked 1 min ago

AndroAndro

1

New contributor

Andro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

CREATE TABLE readings (

   id int(11) NOT NULL AUTO_INCREMENT,

   value double NOT NULL,

   created_dt datetime NOT NULL,

   device_id int(11) NOT NULL,

   PRIMARY KEY (id),

   UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),

   KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id),

   CONSTRAINT readings_device_id_6a03c4ab761154d1_fk_device_id FOREIGN KEY (device_id) REFERENCES device (id)

) ENGINE=InnoDB AUTO_INCREMENT=138611438 DEFAULT CHARSET=latin1

SELECT value, created_dt FROM readings WHERE (created_dt BETWEEN '2019-03-17 19:11:00' AND '2019-03-18 19:11:00' AND (device_id) IN (10, 11, 12));

share

asked 1 min ago

AndroAndro

1

New contributor

Andro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 1 min ago

AndroAndro

1

New contributor

Andro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 min ago

AndroAndro

1

New contributor

Andro is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 1 min ago

AndroAndro

1