CODEWITHSUNDEEP
mysqlperformancequery-optimization
Martin Ille
Martin Ille

Reputation: 7055

Optimizing MySQL SELECT from huge table

I'm doing regular

SELECT * FROM sensor_data_1sec LIMIT 491160, 30

from this table: http://test.dev.brown.sk/sensor_data_1sec.zip

It tooks about 0.06s. I think it is too much. MySQL server is on my PC with Intel Core i7, 8 GB RAM, Windows 7.

Take a look on phpMyAdmin: enter image description here

Of course it took much little time when it is cached. But is it possible to make it faster uncached?

Upvotes: 0

Views: 230

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

It's slow because off the deep scan. An large offset in the LIMIT is bad very bad, if you run an EXPLAIN you will see it needs a temporary table to handle this. And this temporary is proberly disk based because it needs the memory off 491190 records. So you are killing the MySQL performance now.

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881223

Six hundredths (or two hundredths) of a second is fine. The basic rule is that a user shouldn't be kept waiting for too long and I generally put that at half a second.

Is there something you may have acheived in that 0.06s that you're bothered about, had you known it was going to be that slow? :-)

As an aside, it's unwise to assume a particular order in a result set from a query with no ordering clause. It's also usually preferred to explicitly select columns rather than using *.

Upvotes: 1

Related Questions