2

Is there any way by which i can calculate the time that would take for a set of Mysql Queries to execute, may be based on the number of records or something, before executing the query?

joydeveloper
  • 21
  • 1
  • 2
  • Probably not accurately. It depends on the amount of data in the table, number of records being retrieved, indexes on the table, latency, etc. – Travesty3 May 22 '12 at 12:58

1 Answers1

3

No. There are many factors that go into how long a query takes to execute, including:

  • Hardware specs
  • Network configuration, bandwidth, and load
  • MySQL Server configuration and server load
  • MySQL index usage and execution plan
  • Size of data
  • Size of index
  • Size of result set

The best way to determine how long a query will take is to run it. You should disable the query cache using SQL_NO_CACHE so that the query cache does not skew the results.

Use SHOW PROFILE to see where MySQL is spending its time.

Use EXPLAIN to see the execution plan so that you can optimize the execution plan.

Using EXPLAIN, you can get a feel for whether the query is efficient or not, but you will still have to run it to see how long it takes in your environment.

Ami
  • 1,244
  • 6
  • 14
  • I always gauge relative speed. If you have an optimized query that only takes a split second, then that may be a good base line. It does scale. The more rows you have to scan or the more rows you have to return, the slower it will be, and the opposite is also true, but I agree with Ami that you just have to run it to see. Use EXPLAIN to see the number of rows. – Marcus Adams May 22 '12 at 13:17
  • Thanks for the info, one little thing I run into: 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead – Musa Haidari Aug 22 '22 at 10:44