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

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

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.

I agree with gbn, but if you're looking to integrate the ... suffix, you can try:

SELECT   id,
         name,
         CASE WHEN LENGTH(description)>25 THEN
           CONCAT(LEFT(description, 25),'...')
         ELSE
           description
         END CASE AS short_description
FROM     pages
ORDER BY page_modified DESC
LIMIT    10;

Where 25 is the number of characters the preview text should have. (Note this won't split in to whole words, but neither does your PHP function).