Alessandro Ranellucci wrote:
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.
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.
js*******@attglobal.net
Member of Independent Computer Consultants Association -
www.icca.org