Msg#: 4533928 posted 10:29 am on Jan 8, 2013 (gmt 0)
That does not make sense. What do you mean by a page?
You make it as efficient and as swift as possible by allowing as much of the database and indexes to run in the fastest way possible - by running in RAM. You do that by rither increasing the RAM or reduce the size of the database.
The standard engine used in recent MySQL releases is InnoDB, which clusters your data on a primary key. The first n bytes of data are put on a page alongside the primary key, so a lookup by primary key is very fast as is the row retrieval.
This data may end up in the buffer pool https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html if it is continually accessed. I believe the default is up to 768 bytes per record will go into the buffer pool.
If you have a table with large TEXT/BLOB columns, those columns will fill up the 768 byte allocation... and can potentially fill up your buffer pool. This can cause a performance issue if you have data that would be better suited to the buffer pool, like other indexes. The larger data will also have to be fetched from two separate locations because it's larger than the default allocated space.
So saying "The size of the records (too large and it goes over 2 pages)" fits this scenario. The manual and mysql blogs do a better job of explaining it.
Msg#: 4533928 posted 12:30 pm on Jan 8, 2013 (gmt 0)
Hi folks, sorry, I was quoting someone from a while ago... So I'm not really sure myself! - [webmasterworld.com...]
I was worried I was being a bit query heavy in my code, but I guess the only way to really know these things is to properly test it with a bunch of people!...(any effective test methods that don't involve finding a ton of people to all pressure your site a once would be handy? :)
I'm writing a kind of forum type system (with an sql database behind it) and think that some people will be writing a lot of text in it. I've tried various database structures since I started, I had one table for the post text, and another for keeping track of which post follows which post and so on, but then have opted to put it all in one table.
My worry was that with all the numbers I'm using to keep track of which 'conversation' is which and who is replying to what and where, and in what order it all needs to be reproduced, post dates etc the table now has 20 or so columns, including a large chunk of text and a few different dates
As I was adding more columns recently I remembered that previous post and something about 'pages' so thought I'd ask.
Having googled it a bit I find reference to sql 'pages' being 8kb, but beyond that I guess I've some serious reading to do if I want to get my head round it all.
Thanks for the links and pointers :)
I've spent most of my time learning the php involved so far and just hoping my guesses on database structure are good enough!