EchoFool Posted April 21, 2011 Share Posted April 21, 2011 Which is better in terms of efficientcy for the server load etc More tables with less fields and then using inner joins to connect them up in scripts. Or less tables with more fields of the data only needing a SELECT query to get info? Quote Link to comment Share on other sites More sharing options...
requinix Posted April 21, 2011 Share Posted April 21, 2011 What's best is a normalized database. Don't pay attention to how many fields you have until then. Quote Link to comment Share on other sites More sharing options...
EchoFool Posted April 21, 2011 Author Share Posted April 21, 2011 It is normalized but its weather i should split some of my tables up to smaller tables. Quote Link to comment Share on other sites More sharing options...
squigs Posted April 21, 2011 Share Posted April 21, 2011 I've been wondering this myself. Quote Link to comment Share on other sites More sharing options...
requinix Posted April 21, 2011 Share Posted April 21, 2011 What's the structure now? Quote Link to comment Share on other sites More sharing options...
mikosiko Posted April 21, 2011 Share Posted April 21, 2011 It is normalized but its weather i should split some of my tables up to smaller tables. if, as you said... it's normalized... then are you trying to split tables due to the number of records? or you really are not sure if you data structure is correct (and normalized)?... please clarify Quote Link to comment Share on other sites More sharing options...
requinix Posted April 21, 2011 Share Posted April 21, 2011 It is normalized but its weather i should split some of my tables up to smaller tables. if, as you said... it's normalized... then are you trying to split tables due to the number of records? or you really are not sure if you data structure is correct (and normalized)?... please clarify I think the question is about whether to split a (normalized) table with a couple dozen columns into a couple smaller (slightly less normalized) tables. Like if you had a table with columns A-Z, is there any worth to splitting it into a table1 with A-M and a table2 with N-Z (with a 1-to-1 relationship between them). Quote Link to comment Share on other sites More sharing options...
EchoFool Posted April 22, 2011 Author Share Posted April 22, 2011 It is normalized but its weather i should split some of my tables up to smaller tables. if, as you said... it's normalized... then are you trying to split tables due to the number of records? or you really are not sure if you data structure is correct (and normalized)?... please clarify I think the question is about whether to split a (normalized) table with a couple dozen columns into a couple smaller (slightly less normalized) tables. Like if you had a table with columns A-Z, is there any worth to splitting it into a table1 with A-M and a table2 with N-Z (with a 1-to-1 relationship between them). exactly. So to sum up again is a table with large number of fields (forget number of rows) better over multiple tables with less fields... which im not sure why everyone is complicating this with normalization when it has no relationship what ever to it. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted April 25, 2011 Share Posted April 25, 2011 In my experience the only reasons that I've seen to do that (1-1 relationship) is to avoid null/optional columns (empty space) , to implement some kind of sub-typing, or if the table contains fields that are only used for a certain subset of the records. In case of space concerns, I would not bother and live with the empty space. I believe that the hassle it can cause to the development process simply isn't worth it (more "complex" queries/joins), space is cheaper than programming time. For sub-typing or tables with fields identified clearly as a subset most of the time split the model in 1-1 relationship make sense . But, then again, the decision to implement something like you are asking for depend of what exactly are you modeling and your business rules. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 26, 2011 Share Posted April 26, 2011 Don't try and make tables "smaller' -- it's just work for no reason. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted April 26, 2011 Share Posted April 26, 2011 I would say that it depends on what is stored in the tables. if you are storing text fields that will hold a body of text, then you may want to spit it up. For example: You have have database that holds emails, you probably would want one table to store the subject, and one table to store the body. Why? Lets say you have the traffic of Gmail, a text data type has an unknown length, so MySql doesn't have the best idea of where the text ends, so now it has to find start and end points of the data of the text that you won't even use, just to display a list of email subjects. moving the text (body) to a second table, mysql no longer worry about the body of the document just to get subjects. Like a text book (without an index), it would be fairly easy to find all the chapters without having to skim 50 pages to find the end of each chapter if the chapters were in one book with a page number saying where the chapter is in the second book you could get there insanely faster. (no sure on how well this reference is, but it makes sense to me ) This is the same with varchar vs char, varchar usually takes up less space, but char is faster in the long run. Another think you may want to do is learn about inet_aton and inet_ntoa, which converts ip addresses to from 16 bytes to 8 bytes that will reduce a table significantly in size, and makes it about 25 - 50 faster I would say. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 26, 2011 Share Posted April 26, 2011 Not sure where you're pulling those metric from -- 25-50x faster for 8 bytes? Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted April 26, 2011 Share Posted April 26, 2011 Not sure where you're pulling those metric from -- 25-50x faster for 8 bytes? My ass Quote Link to comment Share on other sites More sharing options...
fenway Posted April 27, 2011 Share Posted April 27, 2011 IMHO, over-indexing, either in number or size, is going to be noticeable. Quote Link to comment Share on other sites More sharing options...
requinix Posted April 27, 2011 Share Posted April 27, 2011 Like a text book (without an index), it would be fairly easy to find all the chapters without having to skim 50 pages to find the end of each chapter if the chapters were in one book with a page number saying where the chapter is in the second book you could get there insanely faster. (no sure on how well this reference is, but it makes sense to me ) The only book analogy for your idea that I can think of is a novel where every page is in a different book. Sure, you know the length of each book, but you have to use a bunch of them to get the final story. MySQL stores the length of variable-length text fields. There is no guesswork. It doesn't have to scan for a terminating NUL. And depending on the data type, the data isn't actually stored inline in the table with everything else. In my experience, these kinds of "optimizations" are best left to the people who write the database system. Just use it normally without any fancy "can I develop something convoluted to maybe gain a little bit of $metric" questions. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.