Need huge increase in fields within same table

going from 35 to 150

[henry0]

I need to add about 100+ fields to an existing table containing about 35 fields

I know that only a few people will access together this table at a given time (even with a million visits)

However will the addition have any impact?
Never done any table with that many fields actually can it be functional?

Any advise?

thanks

regards

Henry

[justageek]

Sounds like you may want to rethink your design. Usually that many fields means you might want to think of using a lookup table instead.

JAG

[irock]

What's a 'lookup table?'

[henry0]

good question what is a look up table?

further my design is not the cause and it is way to late for that; it is a functionning large site
and my client needs to add a new section with about 100 variable fields such as number, title, % and you name it!
and those variable fields need to be part of the client CMS end
problem is the new fields to be created need inputs from the original table
therefore the need to add fields to the same table
I'll love to create a new table but how will I grab info from the first one such as ID and more..?
although regardless of any other consideration will a table with 100+ fields be functional?

thanks

[jatar_k]

When you say 150 fields I assume you mean columns.

How many rows will there be in the table?
Will every page access this table for every page load?

It really depends on many things for anyone to say whether extending the table will slow the site noticably. Is a 150 col table a big deal? Maybe but it is hard to say. You said you have to put those extra cols in there so it doesn't really matter.

[henry0]

Hello Jatar_k

Yes indeed we are speaking about col
if I look at it (which I do often from Php Myadmin structure point of views it will be fields
from pure Mysql position it is of course colums
sorry for the confusion.
rows will not exceed 15
total col in the range of 150 very max

I am more concerned by the feasiblilty but it seems that SQL will still perform OK
I was not even sure that "150 number" can be reached without many problems

and yes I know for sure that the table will see very low usage

when you say
<<it really doesn't matter
>>>
I'll say yes and not
yes I need to do it
unless there will be a more elegant solution which I am more than willing to learn about
and yes it matters cause if the results is unworkbale
i'll be back to the beginning!

thanks

Henry

[jatar_k]

150 col
15 rows

It's a baby table and shouldn't impact the speed of mysql at all. What I think may impact the speed of pages is that now, with PHP, you are handling 150 vars instead of 35. You could easily slow down the script if there is too much handling involved.

Just try it in some non-public corner of the site (unless you have a development server) and see what load times are like.

[henry0]

I will try it from my home dev server and report

thanks

[justageek]

I use lookup tables for things such as attributes for items.

Let's say I have a table of people. I can make it so the table has a field for firstname, lastname, hairclolor, sex, age and on and on. If I put that table into production and some sales critter comes along and says we need to have another attribute called iq my only choice then is to add another field called iq. Not good to mess with the prod table structures.

So I have master tables that really just have links to lookup tables for this. So now I have a master person table that points to a lookup table for the attributes. So now when someone wants to add iq I just add another row in the lookup table. One for each unique id which is also the number of people in the master. I can add them all day long without doing an alter table command on a prod db.

JAG

[henry0]

justageek
how do you establish relationship between for ex
master table
and secondary_1 and secondary_2

could you type an example
showing how to query
category field from secondary_2 and using ID from master

greatly appreciated

[justageek]

Sure. My primary key for a mastertable is usually and auto increment int called ID.

So maybe a people master will have 3 fields, ID, Person_ID, Password. Id is that auto increment. Person_ID is whatever id scheme I'm using at the time is and password is a user defined value. For this example I'll just say that the Person_ID is the same number as the auto increment. So now I have one record with a value of 1 for the ID and 1 for the Person_ID and 'billyjoebob' for the password.

Now I create a lookup. It's key is the Person_ID. It may have three fields in it. Person_ID Attribute_Name, Attribute_Value. It may have two records for that Person_ID. Those records might be 1, Fname, Billy and 1, Lname, Joebob.

Now I do my lookup.

select Person_ID from master where Password = 'billyjoebob'

If there is a result then I select the attributes I want.

Select Attribute_Value from lookup where Person_ID = 'result from first query' and Attribute_Name = 'Fname'

This would result in the returned value being 'Billy' from the lookup table.

Want the last name?

Select Attribute_Value from lookup where Person_ID = 'result from first query' and Attribute_Name = 'Lname'

Sometimes I'm not good at explaining myself so please let me know if I didn't do it good enough this time and I'll try again :-)

JAG

[henry0]

Jag
you certainly did a fine job
thanks for the tutorial

it's a great help

now I see the realationship established in between those tables

cheers

Henry

[justageek]

Glad I could help :-)

JAG