CODEWITHSUNDEEP
phpmysql
W.H.
W.H.

Reputation: 17

Does the size of a table affect speed when selcting a single row using the where clause

I may just be paranoid but I'm worried that my blog will eventually begin to run slowly by using a single table with many rows and many columns.

I have a single table for my structured blog posts which contain short numeric data as well as large chunks of text.

The columns in my table are:

ID . Tag . Title . Subtitle . Introduction . Paragraph01 // Paragraph10 . Notes . Photo (jpg/png) . Embed

So the problem is, if I use SELECT * FROM table WHERE id = '' to select a single row from 10,000 rows, will this make the load time of my web page slow?

I would assume that selecting only one row from the table is faster than if you were selecting 50/100/1000/+, but im not sure.

How does the table get read? Does it search the id column for the row before reading the rest of the columns, or does it read all of the data before filtering out the results?

Any advice on how to make it faster if this table strucutre is a problem?

Upvotes: 0

Views: 364

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

Short answer, yes. Long answer, how much depends on a number of factors.

If your table has no indexes on the fields used in the WHERE clause, or the condition involves things that eliminate the use of indexes (like WHERE DATE(whenstamp) >= '20190702'), then the entire table must be scanned; meaning (ignoring overhead of sending the query, the query being parsed, and sending the result), the query time will increase linearly with table size. (1000 records will take 1000 times longer.)

However, if your WHERE condition filters on an indexed field, the amount of the table examined will generally be reduced in a logarithmic fashion. Every doubling of the table size only increases the work by a constant amount.

Upvotes: 1

Related Questions