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" <4rfvgy7@stripped>
To: "Velen" <velen@stripped>; "MYSQL General List" <mysql@stripped>
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/mysql?unsub=1
>
>
