Hi there,

I am looking for a good not "the" way to keep my website/webshop fast with growing complexity of data structure.
I have a self made Perl-based webshop with about 80.000 products and a couple of tables where the main part is a category table a products table and a price table.
There are multiple prices for one product depending on the market, buying amount and customer id.
There are some more tables I have and I want to add to give customers more information about the products.
E.g. properties, relations (groups of color variants, size variants, brand variants).
All the data has a structure which seems more or less good but I am happy enough with the structure.
Nevertheless building a detailed page on demand does a lot of reoccuring requests that cost too much time in my opinion.
There are a couple of requests and a significant amount of Perl-Code before a visitor gets the information about a product. I read a couple of ideas and tried this and that and I am not sure which way makes more sense than another.

A lot of statements say it makes sense to keep the data in the database and build the pages on demand.
As far as I see it makes limited sense to do the same request over and over again.

I tried to store the information in files e.g. as JSON but updating it and the performance did not really make me happy. Keep everything in one file or create one file per information group (e.g. price of the product, variants, properties). Both ways did not really make me happy.

Storing the data in the database as JSON seems to be an option but as my DB is still latin-1 I would prefer not to mix things where as far as I can see JSON should be stored as UTF-8.

I stumbled over freezing / thawing hashrefs and storing them in a DB. No statement I read about that was really emphatic but still it seems kind of elegant in my eyes.

My idea was to make one table with text or blob cols where one col is the price of an item, one is for the properties and so there could be several more.
On each field I could do updates on changes and I could get the whole information with only one request for one item. The structured information is still available in the tables from where I build the hashrefs and maintain the data. So if I would need something special I still could access the data. It seems cleaner and faster to me than using files to cache the information. Stocks are updated every ten minutes, prices are updated on a daily basis and all the other information vary on the source where I get my data from daily to yearly.

I am uncertain because what I read seems to say I am going the wrong way. What I have experienced says the ways I have tried are not very good.

I would be very happy to get some opinions that could direct me to a good way to go on.

Thank you very much

derion

In reply to performance with mysql / file-caching / hash reference on demand by derion

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.