Menu
I have a system (develop by someone else) where all registered user can query data (similar to data.stackexchange.com). The system is getting big and more user query the system and during the high traffic time the database is slow and I am afraid of security now.
I have a very basic knowledge of mysql and databases and I want to learn. Can you point where I need to look and what can I do? (I would like to build my self, so please no code)
974
The Use cached results option reuses results from a previous run of the same query unless the tables being queried have changed. Using cached results is only beneficial for repeated queries. For new queries, the Use cached results option has no effect, though it is enabled by default.
Result Cache is a new feature in Oracle 11g and it does exactly what its name implies, it caches the results of queries and puts it into a slice of the shared pool. If you have a query that is executed often and reads data that rarely changes, this feature can increase performance significantly.
SQL Server does not cache the query results, but it caches the data pages it reads in memory. The data from these pages is then used to produce the query result.
To cache a query, go ahead and save the query first. Fig 1: Press the button to "Save" the query. Then, to cache your most important queries select the “Enable Caching” checkbox and enter a refresh rate.
Well, you have two large jobs to do :)
How to make the system more secure? Well, use SSL where you need to. If the data is not important you can get away without it. That said, if you want to ultra-secure your logins, then insist on HTTPS. Above that, ensure that you never compare passwords directly, rather you compare the hashes of the passwords (with the inclusion of a salt). Additionally, if your website allows people to be remembered, use a token-based approach. This allows you to assign a unique cookie ID with the client for a period of time that it is valid. It's not fool-proof, but better than nothing. Paired with your SSL login requirements, it will be pretty good.
Have a look at cache managers. But before you do, have a gander at what is taking the most time. What particular pages are hitting your website the hardest? Once you ascertain that you can come up with a caching strategy which is, unfortunately, completely website-dependant. What works for one site, would be inadmissable for you. You can use some kind of memcache to store the common stuff so that the basic "Front page" and "Portal" queries are cached efficiently. The rest will have to be dealt with in the regular way.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
