MYSQL Database Problem in my website.?

By sathyendrav ·

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,

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Maybe this will help..

by Shellbot In reply to MYSQL Database Problem in ...

I'm not a MySQL user..but the principles should be the same.

1. Make sure your index's are relevent. Having an index on the wrong column can make things worse :)**_1/Optimizing-MySQL-Queries-and-Indexes.htm

2. Not something I know about, but have a read of this:

3. Obviously not making much of a difference..

4. Thats the whole idea behind relational databases. How else do you link the tables? Are you creating other ID columns and using those?

For example:
Client table
Client_ID (primary key), name, address
Order table:
Client_ID, item ordered, item price

Client_ID links the two tables together.

Collapse -

Thanks for the reply.

by sathyendrav In reply to Maybe this will help..


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,

Collapse -

Slow query log

by Choppit In reply to MYSQL Database Problem in ...

Try enabling slow query logging for clues.

(don't forget to disable it once you've collected the information you need)

Once you have your slow queries you can use EXPLAIN (insert slow query here) to look for ways to optimise.

Also, don't discount the possibility that this could be a hardware capacity issue. Look for CPU, memory and I/O bottlenecks.

Related Discussions

Related Forums