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

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,strrpo开发者_高级运维s($text,' '));
$text = $text."...";        
return $text; 
}

---

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).

---

My POV (which may be wrong!) is that PHP is used to parse the stuff from the server, send it to the DB, and then present it to the client. I prefer to use stored procedures in the database - because it is easy to know what queries are going to be executed and to ensure that the business logic is adhered to.

I just think that having these definite lines is a good idea.

Forgot to mention - The database knows more about the structure and nature of the data than a PHP script.

---

General Rule-of-Thumb:

• Keep substring functions out of the WHERE clause because of the scalar nature of having to compare several columns in WHERE clauses.

• Use substring functions on columns because there is a significant bottleneck between the database server and the database client.