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,