0

I have a database with 1 million records and I frequently run this very simple query:

select id from my_table limit 1000000;

It takes around 5 seconds to execute. And if I add more columns to the select it takes up to 10-15 seconds.

Is there a way to speed up this query? I know I select a large amount of data, but it is necessary in my case to do that. Can I speed it up somehow?

I tried to add a index on the columns I include in the select, but there is no difference on the performance after that.

The database is static and the data inside the database will not change.

1
  • 1
    Can you provide more details to help us to help you? We are missing very critical information here which prevents us to be able to troubleshoot. - What is the table design? Can you execute this query and share the result? That way we can understand whether id is a primary key or it has an index on it or it is an integer column and so on. show create table my_table; - Do you have concurrent queries trying to access same table while you are trying to retrieve one million ids? This may cause a resource contention and locking. Commented Aug 20, 2019 at 8:14

1 Answer 1

1

Since you're reading every record in the table, the database will simply pull everything you want, wrap it [all] up and send it back across the network to your client machine. The more data you pull, the larger that payload is going to be and the slower it's going to travel.

I frequently run this very simple query

The database is static and the data inside the database will not change.

To me, these two statements makes no sense together.

If the data does not change, then you should read it once, perhaps at application startup, and hold it in the client application. Don't keep re-reading the same thing over and over again. That's pointless.

Then there's the question of what you're doing with all this data after you've retrieved it from the database. I sincerely hope you're not putting it all on a screen in front of a User - a million rows to read through? No thanks!

I think you need to explain your situation a little more.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.