CODEWITHSUNDEEP
mysqldatabaseperformancequery-optimizationsubstring
Ali
Ali

Reputation: 267049

In MySQL, Is SUBSTRING(1, 200) better than fetching the full value of a TEXT column?

Say you have a TEXT column on your table which could either be huge / paragraph long rants, or only a few sentence long lines.

Performance wise / server load wise, is it better to do:

SELECT SUBSTRING( myTxtColumn, 1, 200) FROM myTable AS myTxtColumn

Or to do:

SELECT myTxtColumn FROM myTable

Which of the queries puts more load on the server?

I'm curious if it will be easier for the server to fetch the full value of the column than doing a SUBSTRING() on it, or if the SUBSTRING() will be easier since its only returning the first 200 chars rather than the several KB long text values.

Upvotes: 3

Views: 506

Answers (1)

Keith Randall
Keith Randall

Reputation: 23265

I'd err on the side of caution and use the substring. Your first query may need to read the whole string before doing substring on it (I suspect mysql is smarter than that, but who knows). Your second query definitely has to read the whole string and send it over the network.

Upvotes: 2

Related Questions