Big database: many SQL tables vs. object serialization

[Alessandro Ranellucci]

I'm building a PHP+MySQL application for a large website, with 100.000+
records in the main table, each one with a lot of dependencies in other
SQL tables. Thus each page view requires many SQL queries and/or joins.

Since I want to optimize performance, I'm wondering whether it would be
better to serialize full objects instead and store them in a single
table's TEXT field. This way I could just retrieve the row and
unserialize it, avoiding other queries. So far this is my comparison:

Pros:
- only one query per page view

Cons:
- not only a bigger database, due to serialization overhead, but a
single HUGE table (does this slow down things more than doing many
queries?)

Of course there are other drawbacks I actually don't care, such as
getting a very PHP-specific database or limiting retrival to IDs.

Does anybody have any advice about this? (Other than "try and benchmark
it" :->)

Alessandro.

[Matt Mitchell]

Re: Big database: many SQL tables vs. object serialization

"Alessandro Ranellucci" <alex@primafila .net> wrote in message
news:alex-75D159.10461125 022005@powernew s.iol.it...
: I'm building a PHP+MySQL application for a large website, with 100.000+
: records in the main table, each one with a lot of dependencies in other
: SQL tables. Thus each page view requires many SQL queries and/or joins.
:
: Since I want to optimize performance, I'm wondering whether it would be
: better to serialize full objects instead and store them in a single
: table's TEXT field. This way I could just retrieve the row and
: unserialize it, avoiding other queries. So far this is my comparison:

Well, I'd suggest looking at your database design first. It may well be
that if your data is in full 1NF, that denormalising it a *little* may
improve performance in terms of speed. Look at indices on joins, the types
of queries you're running, and the kind of data you're storing.

[Chung Leong]

Re: Big database: many SQL tables vs. object serialization

"Alessandro Ranellucci" <alex@primafila .net> wrote in message
news:alex-75D159.10461125 022005@powernew s.iol.it...[color=blue]
> I'm building a PHP+MySQL application for a large website, with 100.000+
> records in the main table, each one with a lot of dependencies in other
> SQL tables. Thus each page view requires many SQL queries and/or joins.
>
> Since I want to optimize performance, I'm wondering whether it would be
> better to serialize full objects instead and store them in a single
> table's TEXT field. This way I could just retrieve the row and
> unserialize it, avoiding other queries. So far this is my comparison:
>
> Pros:
> - only one query per page view
>
> Cons:
> - not only a bigger database, due to serialization overhead, but a
> single HUGE table (does this slow down things more than doing many
> queries?)
>
> Of course there are other drawbacks I actually don't care, such as
> getting a very PHP-specific database or limiting retrival to IDs.
>
> Does anybody have any advice about this? (Other than "try and benchmark
> it" :->)[/color]

Instead of the standard PHP serialization functions, use WDDX serialization.
Then you wouldn't have a PHP-specific database. More importantly, you can
claim to be in the forefront of technology with your "XML database" :-)

[Jerry Stuckle]

Re: Big database: many SQL tables vs. object serialization

Alessandro Ranellucci wrote:[color=blue]
>
> I'm building a PHP+MySQL application for a large website, with 100.000+
> records in the main table, each one with a lot of dependencies in other
> SQL tables. Thus each page view requires many SQL queries and/or joins.
>
> Since I want to optimize performance, I'm wondering whether it would be
> better to serialize full objects instead and store them in a single
> table's TEXT field. This way I could just retrieve the row and
> unserialize it, avoiding other queries. So far this is my comparison:
>
> Pros:
> - only one query per page view
>
> Cons:
> - not only a bigger database, due to serialization overhead, but a
> single HUGE table (does this slow down things more than doing many
> queries?)
>
> Of course there are other drawbacks I actually don't care, such as
> getting a very PHP-specific database or limiting retrival to IDs.
>
> Does anybody have any advice about this? (Other than "try and benchmark
> it" :->)
>
> Alessandro.[/color]

Alessandro,

I'm with Matt on this one. Look at your database design and go from
there. The *correct* indicies will help performance (remember - you can
have too many indicies just as you can have too few).

But if you're going to serialize the object, why keep it in a database
at all? If you need this level of performance, put it in flat files and
create your own index structure. It will be much faster than anything
MySQL can do.

However, chances are you don't need this speed. After all, 100K rows
isn't very big at all. 100M rows is getting big; I've worked on
databases with 10G rows (not all one table).

If you can stuff everything into a single serialized object, then with
proper database design you should be able to retrieve everything from
the database with a single query.

--

To reply, delete the 'x' from my email
Jerry Stuckle,
JDS Computer Training Corp.
jstucklex@attgl obal.net
Member of Independent Computer Consultants Association - www.icca.org