0

I am new to working with databases and am unaware of their capacity in general. To be specific, I want to know much much time would a particular mysql query take. I tried searching on the internet but nowhere was I able to find an estimate neither a way to find an estimate.

So my question is if there are 900k rows containing id (Numbers from 100 000 to 900 000) in ascending order in a table. How long would it take to search a row with a particular id?

I figured that if I ask someone who has worked with databases at this scale the estimation would be known to that person.

  • With an index, "no time". – Rick James Dec 5 '15 at 0:17
0

The execution speed of a database server depends on a great many factors, for example

  • concurrent load, what else is happening - that is if the database is busy doing something else your query may have to wait
  • CPU speed, if the basic computing power of the database is slow your query will be executed slower than it would be otherwise
  • available disk IO bandwidth, if the data is not cached in RAM and has to be fetched from a disk the speed of the disk will affect performance
  • structure of the data, is it indexed to allow the DB to find your row more quickly?
  • network speed, how long does it take for the query to arrive at the DB and for the result to return to you app?

However to seek a single row out of 800,000 rows on a single table assuming the presence of an index on the column "id" that is specifying the row of data you want, it is likely to take a tiny fraction of a second on modern hardware. In fact the network round trip to your database server is likely to take longer than the database operation.

  • Thanks !, Will look into the mentioned points in detail !! – CodeAllDay Nov 1 '15 at 18:00
  • Hey, can u also let me know what time would it take if the number of rows are 500 million and 1 billion respectively presuming the above conditions by you !. Also does the time required have any relation to number of rows if hes what type as in exponential , etc.? – CodeAllDay Nov 3 '15 at 5:15
  • Index seeks do not get exponentially slower as the number of rows increase. It is much more related to a logarithm of the number of rows... this is due to the B-tree structure of the index. So squaring the number rows only doubles the cost of the index seek, however this all assumes that all resources are available as required by the database (disk IO, network IO, CPU and RAM), eventually one or all of these resources will encounter it's performance limit, that is ultimately the machine can only do so much. – MNB Nov 4 '15 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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