Welcome to WebmasterWorld Guest from 22.214.171.124
Forum Moderators: open
Now my fact is that, for couple of hours on a particular day approximately 20000 users will try to query the database. In addition, only select statement will execute. Iíve no use of insert, delete or update statement on my application.
Is MySQL 4.1.13 capable of absorbing this kind of hit?
Do I need to use replication?
Or how can I improve the performance?
Any other suggestions are welcome
Thanks in advance
I'm no MySQL expert either (SQL Server here), but my #1 suggestion would be: create the right index(es) to support the query!
There is a performance hit when you create/modify an index; however, you say that your app is "select-only"; and an index can make a huge difference when retrieving data.
If your select statement looks like this:
SELECT a, b, c FROM t
WHERE a = 123 and e < 234
you'll probably want an index on the columns a and e.
In addition, you may want to look into using "stored procedures" (not sure if MySQL supports these, but if it does, they're probably faster than "ad-hoc queries").
The first thing I thought of was max_questions. I think it's set to 50000 (it was on my host) and with 20000 daily visitors it could potentially be exceeded. I was able to get the limit raised on one account, but on another I was told they can't raise the limit (confused look).
The problems of too many connections or too many questions doesn't exactly address the issue of improving search performance, but they should not be overlooked. It's advisable to have a 'plan B' in case either becomes an unexpected issue.
InnoDB works well with large tables, but I think that if you don't use transactions MyISAM will work faster... I don't really remember, but I think that the query optimization in such case doesn't depend on type of table.
I was waiting 9 hours so far for 23,760 lines of data to use the UPDATE statement from my PHP, to get it into MYSQL. I Indexed the UPC code, and instantly all of the data went in. I didn't wait more than 5 seconds.
Eltiti you rule ;) - I been trying to do this for years, but no one ever posted this information.