CODEWITHSUNDEEP
mysqlsqldatabaselaravelselect
adir1521
adir1521

Reputation: 142

How to reduce select * from table_name query execution time for table with 200k+ records?

I have mysql table set in RDS and I am querying from my web application. The query itself takes more than 20 seconds while other customized operations takes less than 0.5 seconds combined. Is there any way to reduce the query execution time to 2-3 seconds?

I tried indexing but it doesn't help either.

$table_data = select * from table_name

I expect the above query to take as less time to execute as possible.

Upvotes: 0

Views: 371

Answers (1)

The Impaler
The Impaler

Reputation: 48770

Here's the thing:

  • Your query has no filtering predicates (no WHERE clause). Then you are selecting all the rows of the table.
  • Your query has a * instead of a subset of columns. Therefore, it's selecting all the columns of the table.

In sum you are getting the whole table, every time.

There's nothing you can do on the SQL side. Your chances now reside on the admin side of the database. Get more hardware (faster CPU), increase paralellism (more cores), a faster SSD maybe?

Alternatively, you could use caching on your app side. Run the query only once every 5 mins, and keep the rows in memory.

Upvotes: 4

Related Questions