homepage Welcome to WebmasterWorld Guest from 54.226.213.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
database speed and results pages
Mr_Cat




msg:4533930
 12:56 pm on Jan 7, 2013 (gmt 0)

Hi folks,

I was inquiring a while ago about how to make my database as efficient and swift as possible, and was told that one thing that could really slow things down was:

"The size of the records (too large and it goes over 2 pages)"

My question is what/how big is a page and how do I find out if my records go over a page?

Thanks

 

Frank_Rizzo




msg:4534212
 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.

brotherhood of LAN




msg:4534218
 10:51 am on Jan 8, 2013 (gmt 0)

MySQL I assume

It really depends on how interested you are on the MySQL internals InnoDB Page Structure [dev.mysql.com]

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.

topr8




msg:4534219
 10:54 am on Jan 8, 2013 (gmt 0)

there are so many factors.

however as Frank_Rizzo says your question doesn't make sense!

are you confusing database speed and the time the webpage takes to render in the browser?

most likely there are three factors,

1) your php code and how long it takes to connect to the database, how long the database takes to process the commands and then send the data/recordset back to the php cade.

2) how long your code takes to process the recordset/s returned by the database and turn it into html.

3) how quickly the 'html' is sent to the browser and how quickly the browser renders it.

Mr_Cat




msg:4534243
 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!

Cheers folks

brotherhood of LAN




msg:4534249
 12:39 pm on Jan 8, 2013 (gmt 0)

any effective test methods that don't involve finding a ton of people to all pressure your site a once would be handy


[dev.mysql.com...]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved