Can someone please explain to me what impact Length / Values has on a table?

 

I'm just a beginner when it comes to MySQL and i'm trying to insert some data into a table but it's not showing up. I think it's because the Length / Values number in the structure of my table is lower than the amount of characters of the data i'm trying to insert.

 

So basically what difference would it make if this number was just set to 1000?

 

Apologies if this is a stupid question.

 

[EDIT]: I'd still like an answer to the question above, but i've managed to figure out that it's something else that's causing the trouble, basically I have a cronjob set up to insert data from multiple sources, into the table, everythings been working fine up until now, but basically it seems the cronjob gets so far and then just stops, so i'm only getting half the data inserted.

 

Any idea why this is happening?

Edited June 17, 201214 yr by Lee_K

It could be that you are trying to put to much in OR there is somthing in the data that the field is not happy about. Apostrophes and MSword special charecters being a particular bain of my work.

 

 

 

PS: are you using BULK INSERT inside that cron job script or looping through the data inserting one row at a time?

 

 

Dynamic Vs Fixed Optomisation

Best analogy I was told was to think of it like building a bookcase.

 

It being a simpler set of instructions to build one that has fixed diemensions than

one that is flexible and can change shape to accomadate whatever you try putting on/in it. So the less code the inner workings of the database has to chew through the faster it can do its job.

 

At its core a database is basically a flat document like a CSV file and if you ever try looking at one of those in notepad you will soon discover that fixed lengths equals neater making them easier to read and the same applies to any script trying to fetch data out of them.

 

Consider how items from a shelf of unknown length are extracted..

have I got to the end marker of the shelf? if yes stop | else get next item and the loop goes on till it reaches the end. Because you don't know how much is on that shelf you don't know wether you'll need a hand cart or a truck to move it. Or rather how much RAM will be needed to hold it and CPU cycles used to get it.

 

with fixed length..

get every item off the shelf numbered from one to 1000 and use a hand cart Simpler detailed instructions for getting results off the shelf faster.

 

Therefore each request consumes a known amount of server resources and you can plan ahead knowing how many requests can be handled in a given time period. You know how long every connection to the database is going to be open and how long each script chewing on the data will take before the visitor gets to see what they asked for.

 

Ultimately this kind of optomisation is all about squeezing the most out of the server before the need to upgrade becomes a necessity. Most sites will never need to worry about this unless they are getting hordes of visitors or have really awful hosting.

Edited June 17, 201214 yr by Sogo7

Thanks for the reply, that's cleared a few things up.

 

I changed a few things round in the cronjob so that different sets of data would insert before others and the stuff I couldn't get to show up before is now showing up. But when I browse my table it says at the top:

 

'Showing rows 0 - 29 ( 42,060 total, Query took 0.0003 sec)'

 

Basically I can't seem to go past 42,060 rows! That's where my problem is! What's caused that and what can I do about it?

 

Here's the info on my table:

 

Space usage Type Usage

Data 27,467.7 KiB

Index 1,577.0 KiB

Total 29,044.7 KiB

Row Statistics Statements Value

Format dynamic

Collation utf8_general_ci

Rows 42,060

Row length ø 668

Row size ø 707 B

Creation Jun 17, 2012 at 10:52 PM

Last update Jun 17, 2012 at 10:53 PM

Last check Jun 17, 2012 at 11:03 PM

Open new phpMyAdmin window

 

Thanks for the help! It's greatly appreciated!

Think i've managed to fix this by myself! It was all to do with my Unique Index, and duplicate data. It was leaving numerous rows out from most sets of data. But anyways it's fixed I think. I just selected 2 columns to act as a unique index and now everything is where it should be... Does that sound right? Thanks for the help!