CODEWITHSUNDEEP
phpmysqldatabaseperformancedatabase-performance
repoleved
repoleved

Reputation: 133

Performance: fetching data from Database or replacing it to make new data

I don't want to start a discussion so I am keeping my question to numbers.

I have a table in my MySQL database that has 3099916 records. Now two columns of the table are the same except for a single character.

In my case they are URL and Thumbnail_URL from the same image.

Size of URL string is 72 characters.

     like image URL is  12345sss_a.jpg
while Thumbnail URL is  12345sss_b.jpg

Now what'd be better for me, i.e the most efficient way

  1. To fetch both URL's from table.
  2. To fetch one from table and make 2nd by replacing a character.

Upvotes: 1

Views: 114

Answers (4)

Mohammad Mahroz
Mohammad Mahroz

Reputation: 462

Depends on size of your data base.

Under current circumstances replacing is much much better, because your table is pretty much big.

Upvotes: 1

Mukul Medatwal
Mukul Medatwal

Reputation: 170

You can only store the uploaded image name into the database and upload the real image and also thumb images to server and give their name like imagename_80x80.jpg, imagename_200x200.jpg.

When you want to show any image, just get the real name of the image from the database and put a postfix image size that size's thumbnail you will want.

In this way, you can upload multiple thumbnails and improve your query performance.

Upvotes: 1

Rick James
Rick James

Reputation: 142453

Store only 12345sss. When SELECTing, tack on the suffix to get the image or the thumb.

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29649

Plus one for the benchmarking.

But actually, I doubt you'll be able to tell the difference unless you have a resource constraint somewhere.

This is all based on assumptions, but I guess you have PHP code that retrieves the image information based on some criteria. The expensive part in this is the "where" clause - if that's slow, everything else will be slow.

The number of columns you return from this query makes no noticable difference on performance, unless you are returning a HUGE number of columns, or the columns are very large.

So, you may as well retrieve both image and thumbnail columns in the same query.

On the other hand - the string replace function in PHP is pretty fast too, so again - unless you're showing a LOT of images on the page, you're unlikely to see a measurable difference. And if you're showing a lot of images on the page, download time and browser rendering is much more of an issue than the PHP string replace...

Upvotes: 0

Related Questions