• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Moving BLOB column to a different table

 
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I have 20 columns in a table which will be frequently accessed. Out of 20 columns, 2 of the columns of type BLOB however it will be very rarely used.

Currently we are facing performance issue and moving 2 BLOB columns to a different table..Now the speed is fast...

Can someone explains why it is fast ?? What's the drawback in having BLOB columns ???

Does any select query performance is affected when a BLOB column exist in a table??

Please advise the best approaches in normalisation whenever we come across BLOB or CLOB

Thanks
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Simply put, the more data you select, the slower it is to get the data to you. Especially if it's coming across a network connection. And a BLOB or CLOB can contain a large amount of data.

Of course if your query doesn't select those BLOB columns, then you aren't selecting the data in them, so you don't have that problem.

So the conclusion should be, if you don't need those columns in your program then don't include them in your query. But that's a standard rule, your queries should only select the columns they need. "SELECT * FROM TABLE" is likely to be lazy programming.

As for normalization, putting the BLOB and CLOB columns in a different table is a major step backwards in normalization. Normally all data which depends on a certain set of keys should be in the same table.

Many database systems have the ability to create a "view" over a table which can contain a subset of the columns. So before taking the radical step of putting the BLOB columns in a separate table, it would be worth considering fixing your code to not use lazy "SELECT *" clauses or using a view which excludes  the BLOB columns. Or both.
 
Johnny Joseph
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Paul,

i come across this link - https://stackoverflow.com/questions/9511476/speed-of-mysql-query-on-tables-containing-blob-depends-on-filesystem-cache

which says query depends on the BLOB/CLOB column as well eventhough we didn't select the columns

Please go through and add your comments

Thank
 
Paul Clapham
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sounds from the discussion there as if it might be specific to MySQL.

My StackOverflow account is long dead, I haven't posted there for years.
 
Johnny Joseph
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul, then let's leave MySQL.....How about SQL, Oracle..When we select only few columns other than BLOB/CLOB will there will be an impact due to huge records because we have those columns in the table...

Also, update column other than BLOB/CLOB..
 
He's giving us the slip! Quick! Grab this tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic