Welcome to WebmasterWorld Guest from 18.104.22.168
Forum Moderators: open
I have database a tables tbmsg and tb_profileview which now contains 15 million rows and growing - I need to write and retrieve data in this table simultaneously. The problem is that upon execution of the query[read/write] the succeeding other queries will locked which causes mysite.com to load very slow. I'm using MySQL database with MyISAM as an storage engine used.
What is the best way to get rid of it?
Have a look at delayed inserts:
The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete. This is a common situation when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete.
Caching, either to files or to another DB can help a lot in situations like this (they MySQL query cache probably won't help much though as it's flushed whenever you modify an entry).
What data is being written each time?
What data is being read each time?
If you can isolate the changing data into it's own table or db that might help.
Also, your userID should probably be an int(10), not a varchar(32).
Probably the same thing for viewer, which I assume is really a foreign key.
What is impression? Does it need to be a KEY?
Do you really need a primary key that spans two fields? Perhaps it would be better to have one unique id be the primary key (this is what I usually do for every table).
And, as physics mentioned, knowing what data is being written/read will further help us understand the specifics of the problem.
Data that is being read each time...`userID` is used for query condition purposes, `viewer` and `date` are selected fields to be displayed.
You're right `viewer` is a foreign key
`userID` is of VARCHAR type 'cause it will store alphabet and number values.