good evening,
i have a mysql table that contain the users data
and i use mysql_connect, mysql_query, mysql_fetch_array to fetch user data
this query should run on every page load and for thousands of users
so which is better and faster ? using mysql for every page load ?
OR cache all of the user data results in a file and include it ?
-
Why don't you cache the mysql query using memcache?BRampersad– BRampersad2011-10-02 01:00:47 +00:00Commented Oct 2, 2011 at 1:00
-
1Do you already have thousands of users or it is just your dreams?zerkms– zerkms2011-10-02 01:00:57 +00:00Commented Oct 2, 2011 at 1:00
-
It depends on a lot of things, primarily the speed of your local disk vs that of the mysql server, whether or not mysql's query cache is enabled, how volatile the data are, etc. This is per-user data? How often does it change? How much data are we talking about?timdev– timdev2011-10-02 01:01:30 +00:00Commented Oct 2, 2011 at 1:01
-
1@zerkms, no for both .. i am working in a project that will have huge traffic for sure ...al-dr– al-dr2011-10-02 01:06:51 +00:00Commented Oct 2, 2011 at 1:06
-
Also: why are you using deprecated mysql functions for what appears to be a new project? Use PDO.timdev– timdev2011-10-02 01:08:05 +00:00Commented Oct 2, 2011 at 1:08
|
Show 6 more comments
2 Answers
I think the right answer is mix. You should cache the most common query result and retry the other "on the fly"
Comments
If it is user data for logged in users, I would store their user info in the session rather than fetching it over and over again.
If it will be changing frequently and you are worried about database performance, a good option would be to cache the data using something like memcached to cache the data in memory. You could request the data from cache on each request, and if user information changes, simply update the cache and the next time it is fetched it will get the new data and there is no need to hit the database unless the cache entry doesn't exist.
6 Comments
al-dr
hi drew010. i already have session for that . but this session should be checked from the mysql so i use mysql query for it .. i didn't used memcached ever, but storing data in memory will increase the memory load right ? i need something fast and better and not make load in database or memory ...
drew010
The amount of memory required depends on how much data you will be storing per user and how many users you have, it can be on a separate server or the same server as your site. I don't see better options than a memory cache, or fetching from the database if you must. You can enable the query cache in mysql so it can store the results of queries that haven't changed. If user information changes you can write it to a file that can be checked, but this probably doesn't get you much and adds complexity.
zerkms
@al-dr: memory is cheaper than CPU or disk IO. So it shouldn't be an issue for "a big site that already have a huge traffic" to buy some more RAM
drew010
yes, and a distributed cache like memcached helps because if you run memcached on 2 servers and dedicate 2GB of RAM for each cache, you get 4GB of cache space total, not 2GB. This is because it distributes it across all servers. 4GB of cache can probably store a decent number of user's data depending on how much of their info you have to cache.
al-dr
@zerkms, so why using mysql_query to select col1,col2,col3,col4 while we can just use a single query for every column and fetch theme inside a loop ?!!! how you thinking my dear ? why you try to make it difficult ? from your replies you need me first to use any thing, then try to search for code updating and memory load decreasing !!! i do not think it is a smart at all .... thank you ..
|