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.
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?)
The database is using MYISAM. I dont know if this supports my database.
I enabled the query catche to 512MB in the server.
Primary keys: i am using Primary keys only for auto incrementing, can the same PK be used as foreign key in other tables?
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.