How many fields are too many when querying MySQL ?

Hi,

I have read many times that we have to avoid reading too many fields when querying MySQL (or any database)...just query the fields you need.

However I have a project where a table has 100 fields per record/row.

The thing is that, everytime I query the database, I need ALL of the fields for further calculations, etc after the query is done.

I will eventually have some 200 users, which will be using the same query/table. At the moment I have done some continuous testing with 3 PC's at once, constantly querying the database, and there is no performance problem at all, all data is queried at a fraction of a second. Also note the queries are many times the same (all users query the same thing), so query cache is being hit quite a lot..

But I am concerned when all users (+200) at linked and project is in production, I will start having performance problems.

So...the question, is 100 fields too many to query ?

Can SELECT * ... be used or would SELECT field1,field2,field3... etc be faster ?

I don't know how to "simulate" 200 users querying at the same time...any ideas ?

thanks

+1 A table with 100 fields gives off a dubious code smell. :-)

middaparka 2010-08-06 16:35:58

trust me on this one, I have looked at reducing the number of fields, however it is not possible.Although not all records use all 100 fields, most use between 50-60, some 75+, and a few the full 100.I don't have much experience with mysql, but I really have thought about it and don't know how to make it less than 100 fields

griseldas 2010-08-06 16:44:42

thanks Amadan for the link !

griseldas 2010-08-06 16:52:27

There *could* be a valid reason to use 100 fields, but it's certainly pretty unusual, so I'd be sceptical. Are there any related sets of fields, that are typically either provided together or mostly NULL? If so, breaking them out into their own entity could well be a good idea.

bobince 2010-08-06 16:57:25

yes, there are many fields which are NULL, but I don't know when this happens.Sometimes the record uses 50 fields (rest are NULL), other times the record uses 75 fields, and 25 fields are NULL...it depends.Could you elaborate on "breaking them out into their own entity" ?

griseldas 2010-08-06 17:01:14

Well, if some of the columns represent related values, and they were either all `NULL` or all `NOT NULL`, that would imply they had a meaning of their own. As a trivial example if you had `address1`, `address2`, `address3` and `postcode`, those clearly represent part of a single entity, an `address`, which could meaningfully be thrown into a separate table. There'd be a one-to-zero-or-one relation from the users table to the addresses table, so when there was no address you'd only have one `NULL` column in the users table, and no row in the addresses table.

bobince 2010-08-06 17:17:50

got your point, bobinceI'll go back to the drawing board and learn a bit more about SQL before proceeding...

griseldas 2010-08-09 09:12:01

If you need every field, `SELECT *` is still a problem because next time you add something to the database you have to find all of those places, rather than just adding your new column the places you know it will be used.

Brendan Long 2010-08-06 16:56:30

thanks for link and comments.But if I divide the record into 4 different tables for example, but I need all fields for calculations, I will still have to query all 25 fields on each of the 4 tables in order to have all the data I need, right ?

griseldas 2010-08-06 16:57:26

@Brendan: Fair enough. :)

JustBoo 2010-08-06 17:01:47

@griseldas, see comment about joins and subqueries above. I must point out that I understand pragmatic reality-based constraints. I'm not a "normalization nazi." :-) Nor a code or comment nazi either. Good Luck.

JustBoo 2010-08-07 13:47:04

ansaurus

tags:

views:

answers:

How many fields are too many when querying MySQL ?

related questions