1

I have a question. My friend told me PHP is better option but I'm not sure, I think sql might be faster. So basically I'm trying to get 50% of my db record and I use mysql function substr to do that. Of course I can do the same thing in PHP after data fetching, using this function right bellow.

sql

"select *,substr(text,1,(CHAR_LENGTH(text)*0.5)) as text FROM stories"

php

$data=$sql->fetchAll(PDO::FETCH_ASSOC);
$length=count($data);
for($i=0;$i<$length;$i++){
    $data[$i]['text']=substr($data[$i]['text'],0,strlen($data[$i]['text'])*0.5);}

What should I use and why? thank you

Improve this question
5
  • I am biased to SQL and the answer to your question will be primarily opinion based but I guess just try out both and see which one performs better. Commented Mar 26, 2017 at 15:00
  • With select *, .. you are fetching all data including text + the half of the text. Commented Mar 26, 2017 at 15:02
  • @PaulSpiegel yes all data, but using PDO::FETCH_ASSOC it returns only my 50% of text and the rest, no dupicates Commented Mar 26, 2017 at 15:13
  • @AlenTurkovic With PDO::FETCH_ASSOC PHP will overwrite the first column text (full data) with the second one (half data). But before that, both columns are returned from the DB to PHP. So actually you are fetching 50% more data instead of 50% less. As Gordon wrote in his answer, you should only select columns that you are going to use. Commented Mar 26, 2017 at 15:23
  • @PaulSpiegel i'm using all data but yea thats definitely true. i fixed it after Gordons answer. to be honest i didnt know that, i thought its not a big deal, but yea 2k-3k bytes more is too much Commented Mar 26, 2017 at 15:36

2 Answers 2

Reset to default
2

This can be rather complicated, but your case is simple. The best query for you to use is:

select left(text, char_length(text)*0.5) as text
from stories

Notice that I removed the *. You should not return columns that you are not using. This is a no-brainer: Why pass more data back from the database than you are going to use? That just takes more time and uses resources inefficiently.

I also replaced substr() with left(). It just seems more in the spirit of what you are doing.

As to whether you should do the string manipulation in the database or in PHP, it depends on the operation. In this case, the operation is simple and easily expressed in MySQL. And, reducing the amount of data has the bonus of improving communication.

There are other cases where the string manipulation functionality of PHP is much better than MySQL. So, there are cases where it is better to do the formatting at the application level.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

thank you a lot. I love the answers from someone like you
@GordonLinoff could you give an example and/or a general rule for when it's better to do string manipulation in PHP rather than SQL? I'm interested in the peformance aspect, as well as things like maintainability.
@Yury . . . I tend to be biased to doing such manipulations (when not too complicated) in SQL. However, a PHP programmer probably has the opposite bias. In general, you want to do lots of manipulations in the database because it is generally more powerful than the client.
0

As much as your database handling stronger your code will be reduced

As per my opinion go with SQL queries that gives you required results.

  1. It reduces the load of communicate large records with code.
  2. List item speed will little faster
Improve this answer

1 Comment

"It reduces the load .." - not with select *

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.