What's faster: MySQL LEFT(*,100) or PHP substr()?

Question

I am building a simple list of the last 10 updated pages from the database. Each record I need to display: name and shortened/truncated description that is stored as TEXT. Some pages the description can be over 10,000 characters.

Which is better for speed and performance? Or a better way to go about this? I use both Zend and Smarty.

MySQL

SELECT id, name, LEFT(description, 100) FROM pages ORDER BY page_modified DESC LIMIT 10;

PHP

function ShortenText($text) {
// Change to the number of characters you want to display        
$chars = 100;        
$text = $text." "; 
$text = substr($text,0,$chars);
$text = substr($text,0,strrpos($text,' '));
$text = $text."...";        
return $text; 
}

Answer 1

Because your question was specifically "faster" not "better" i can say for sure that performing the calculation in the DB is actually faster. "Better" is a much different question, and depending on the use case, @Graydot's suggestion might be better in some cases.

The notion of having the application server marshal data when it doesn't need to is inconsistent with the idea of specialization. Databases are specialized in retrieving data and performing massive calculations on data; that's what they do best. Application servers are meant to orchestrate the flow between persistence, business logic and user interface.

Would you use sum() in a SQL statement or would you retrieve all the values into your app server, then loop and add them up? ABSOLUTELY, performing the sum in the DB is faster... keep in mind the application server is actually a client to the database. If you pull back all that data to the application server for crunching, you are sending bytes of data across the network (or even just across RAM segments) that don't need to be moved... and that all flows via database drivers so there are lots of little code thingies touching and moving the data along.

BUT there is also the question of "Better" which is problem specific...If you have requirements about needing the row level data, or client side filtering and re-summing (or letting the user specify how many left charatcers they want to see in the result set), then it might make sense to do it in the app server so you dont have to keep going back to the database.

you asked specifically "faster" and the answer is "database" - but "overall faster" might mean something else and "overall better" entirely something else. as usual, truth is fuzzy and the answer to just about everything is "It depends"

hth Jon

Answer 2

LEFT in the database.

• Less data sent back to the client (far less in this case, a max of 1k vs 100k text)
• It's trivial compared to the actual table access, ORDER BY etc

It also doesn't break any rules such as "format in the client": it's simply common sense

Edit: looks we have a religious war brewing.

If the question asked for complex string manipulation or formatting or non-aggregate calculations then I'd say use php. This is none of these cases.

One thing you can't optimise is the network compared to db+client code.