SitePoint Sponsor

User Tag List

View Poll Results: If Designg a MSQL Database for a portal having huge date. What do u concentrate on?

Voters
0. You may not vote on this poll
  • Index's

    0 0%
  • Primary Keys

    0 0%
  • Database Engine

    0 0%
  • query caching

    0 0%
Multiple Choice Poll.
Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL Database Problem in my website.?

    Hi,

    I started working on zencart and customized it according the requirement. The problem in the beginning it used to be fine, after it reached 90000 product records the site is responding very slow. I have added the index to some of the table it become little better and now its again slow. After a little investigation i found the following.

    1) I created the index's in database but when my user tries to add the products by uploading the excel file on the server. The products gets added but not the indexes. (is it required to add the data to index? if so how to add the data to index?)

    2) The database is using MYISAM. I dont know if this supports my database.

    3) I enabled the query catche to 512MB in the server.

    4) Primary keys: i am using Primary keys only for auto incrementing, can the same PK be used as foreign key in other tables?

    Please suggest me the answers to the above.

    Thanks & Regards,
    Satya.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1) no, indexes are updated automatically
    2) yes, MyISAM supports your database, but it may not be the best choice
    4) yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1) no, indexes are updated automatically
    2) yes, MyISAM supports your database, but it may not be the best choice
    4) yes
    Hi,

    Thanks for the reply. All these days as i worked with the people who work with mssql, i want is a misunderstanding that we dont have foreign key in mysql.

    I have missed some information in my previous post. The problem is my site is having the details of 90000 books and 13000 authors and 40000 publishers data. This data will be updated on daily basis and i have given a option for updating the data using excel file. i.e. Operators add the data into the excel file and upload the excel file at the end of the day to same the page loading time. Due to this i observed that tables are getting locked many times at the time of uploading the excel files. Recently the table containing book description crashed. I observed that site is running day by day.

    Now my question is Myisam engine suitable for my type of requirement. What should i do to make site run fine even after 10,00,000 books details.

    Thanks & Regards,
    Satya

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "run fine" seems to suggest that you want your queries to be optimized

    this can be done, but we would need to see your exact table layouts as well as your exact query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •