List:General Discussion« Previous MessageNext Message »
From:Velen Date:August 11 2008 5:28pm
Subject:Re: Help with Table structure
View as plain text  
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
>
>


Thread
Help with Table structureVelen10 Aug
  • Re: Help with Table structureChris W11 Aug
  • Re: Help with Table structureVelen11 Aug
    • Failed to initialize the master info structureCharles Brown11 Aug
      • Re: Failed to initialize the master info structureMichael Dykman11 Aug
        • Re: Failed to initialize the master info structureMoon's Father12 Aug