Thanks Chris for your comments. The table is actually storing CVs of jobseekers. With most fields being Varchar (50), varchar(100), varchar(150).
The database is using MyISAM. I agree with you that the table could be optimized. The problem is that when we setup that table and that project the table was supposed to contain a maximum of 50,000 records, but now that the website is getting more and more vistors, the number of records is expected to be 10 times more than expected. I just wanted to know if it is worth going for a complete re structuring of the db or if this table would still be able to handle the records. I think from your explanation, I better go for a complete restructuring. Thanks. Velen ----- Original Message ----- From: "Chris W" <[EMAIL PROTECTED]> To: "Velen" <[EMAIL PROTECTED]>; "MYSQL General List" <mysql@lists.mysql.com> Sent: Monday, August 11, 2008 19:14 Subject: Re: Help with Table structure > Not sure how someone can intelligently comment on your table structure > when you haven't given any details of the data you are storing. In my > experience, the fact that you have 75 fields in your table is a strong > indicator that your data is not normalized. If that is the case you > tables are likely much larger than they need to be and queries may be > slower. > > In general the larger your tables are the slower queries are going to > be, if it is too slow for you depends on your hardware and how fast you > need it to be. Also not knowing how large the 75 fields are, makes it > hard to make any guess on this either. If they are all INTs or > char(1)s, then that really isn't that much data and half a million > records won't be all that much to handle. > > You also don't mention which database engine you are using. MyISAM will > be much faster than some of the others, if you don't need to do > transactions that would be what I would use. > > Velen wrote: > > Hi, > > > > I have a table containing 75 fields with a primary index and index set on 5 other fields. Everything is working fine so far as the table contains only about 80,000 records. I expect these records to reach 500,000 by end of september. > > > > I would like to know: > > - if the number of records will slow down my queries when I search on the indexed fields? > > - if manipulating the records within the table will be slow, (i.e. insert into table..., delete from table..., subqueries) > > > > Can anyone advise? Also, if you have a similar table please let me know your pros and cons for this kind of table structure. > > > > Thanks. > > > > > > Velen > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
