0

related to another question that no body answerd.

in another question that i asked before some person helped me but now i am stuck in another situation.

ok now i want to know is there any way in some table we use from two seperate engine like 2 column uid and username has myisam engine and lastlogin which is a timestamp has a memory engine in use.

because i thought its not possible i tryed to make a table onlineusers(MEMORY ENGINE) in database with two columns one was uid and another one was lastlogin and then when user wants to login i check database like this and insert if user not exist

$myquery = mysql_query('SELECT COUNT(1) FROM `onlineusers` WHERE uid="'. $row['uid'] . '"');

if(mysql_result($myquery,0) == 0){
    $data['uid'] = $row['uid'];             
    $db->insert("onlineusers",$data);
}else{
    updateStatus($row['uid']);
}

and update status function is like this 

function updateStatus($uid){
    $db = DATABASE::obtain();
    $query = mysql_query("UPDATE `onlineusers` set `lastvisit`=now() WHERE uid='".$db->escape($uid)."'");
}

but when i thought about this i figured it out when we restart our server then table of onlineusers will be empty and what will happen for users with cookies set logged in?

then i have to change update function to use two seperate query first query check if uid exist in onlineuses then if not we will insert into it or if exist we have to update timestamp..

now i have another question to ask. using this method is faster to check if exist or not and then update it with updatestatus function which has memory engine ... or just create some timestamp cloumn in my users table and update it on each page check that have myisam engine??

Community
  • 1
  • 1
HiDd3N
  • 494
  • 6
  • 23
  • 2
    Why not write to disk? Is the InnoDB engine not sufficient? – Matt S Nov 02 '12 at 19:22
  • @MattS i just think about performance and i dont know which way is best using some memory engine table and just check 2 query or use some table with inodb or myisam engine with just one query on each page load update?? – HiDd3N Nov 02 '12 at 19:26
  • *"related to [another question](http://stackoverflow.com/questions/13195426/whos-online-with-timestamp-with-high-performance/13201601#13201601) that no body answerd."* YOU accepted the answer... – Kermit Nov 02 '12 at 19:28
  • 3
    Just use innodb. Mysql will use memory caching for recently accessed records. If you set the memory cache for innodb high enough, it will keep the entire table in memory and persist to disk. A properly tuned mysql server won't have performance issues on something like this until you have millions of users. – hukir Nov 02 '12 at 19:29
  • @njk you are right.but i thought i have to accept it because i wanted to thanks him:)..now i know i have to accept until the question end?am i right?i am noob in stackoverflow – HiDd3N Nov 02 '12 at 19:33
  • realy thanks @njk... ill read it soon – HiDd3N Nov 02 '12 at 19:41

1 Answers1

0

What you are trying to do, i.e. use two separate engines on the same table does not sound possible. A single engine controls a single table as the most basic level of MySQL. It might be possible to get around that, but it would not be wise. You could edit the source, but that would be crazy. If you are worried about performance, then you could either split your table into multiple tables to use the engines you need on the table you need. Otherwise you could use a caching framework like mem cache (which I recommend) that would boost performance via caching however you need it. Mem cache is a well tested PHP tool and it sounds like what you need. Good Luck.

usumoio
  • 3,500
  • 6
  • 31
  • 57