Improve performance of query with large table?

Question

I have a large table named 'roomlogs' which has nearly 1 million entries. The structure of the table:

id --> PK

roomId --> varchar FK to rooms table

userId --> varchar FK to users table

enterTime --> Date and Time

exitTime --> Date and Time

status --> bool

I have the previous indexing on roomID, I recently added an index on the userId column.

So, When I run a stored procedure with following code it is taking more time like on average 50 seconds. WHich it should not take.

    DELIMITER ;;
    CREATE DEFINER=`root`@`%` PROCEDURE `enter_room`(IN pRoomId varchar(200), IN puserId varchar(50), IN ptime datetime, IN phidden int, pcheckid int, pexit datetime)

begin
    update roomlogs set 
          roomlogs.exitTime = ptime,
          roomlogs.`status` = 1  
       where 
              roomlogs.userId = puserId 
          and roomlogs.`status` = 0 
          and DATEDIFF(ptime,roomlogs.enterTime) = 0;

    INSERT into roomlogs 
      ( roomlogs.roomId,
        roomlogs.userId,
        roomlogs.enterTime,
        roomlogs.exitTime,
        roomlogs.hidden,
        roomlogs.checkinId ) 
      value
      ( pRoomId,
        userId,
        ptime,
        pexit,
        phidden,
        pcheckid);

    select * 
       from 
          roomlogs 
       where 
          roomlogs.id= LAST_INSERT_ID();
    end ;;
    DELIMITER ;

What Can be the reason for it to take this much time:

1. I added an index recently so previous rows are not indexed.
2. There is no selection on storage type for any indexes right now. Should I change it to B-tree?
3. On my website, I get 20-30 simultaneous call on other procedures also while this procedure has 10-20 simultaneous calls, does the update query in the procedure make a lock? But in MySQL.slow_logs table for each query the lock _time shows 0.
4. Is there any other reason for this behaviour?

Edit: Here is the SHOW TABLE:

CREATE TABLE `roomlogs` (
   `roomId` varchar(200) CHARACTER SET latin1 DEFAULT NULL,
   `userID` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
   `enterTime` datetime DEFAULT NULL,
   `exitTime` datetime DEFAULT NULL,
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `status` int(11) DEFAULT '0',
   `hidden` int(11) DEFAULT '0',
   `checkinId` int(11) DEFAULT '-1',
   PRIMARY KEY (`id`),
   KEY `RoomLogIndex` (`roomId`),
   KEY `RoomLogIDIndex` (`id`),
   KEY `USERID` (`userID`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1064216 DEFAULT CHARSET=utf8

I can also see that this query is running more number of times like 100000 times per day (nearly continuously).

SELECT count(*) from roomlogs where roomId=proomId and status='0';

Because of this query reads from the same table, does InnoDB block or create a lock on update query because I can see that when the above-stored procedure is running more number of times then this query is taking more time.

Here is the link for MySQL variables: https://docs.google.com/document/d/17_MVaU4yvpQfVDT83yhSjkLHsgYd-z2mg6X7GwvYZGE/edit?usp=sharing

Answer 1

roomlogs needs this 'composite' index:

INDEX(userId, `status`, enterTime)

I added an index recently so previous rows are not indexed.

Not true. Adding an INDEX indexes the entire table.

The default index type is BTree; no need to explicitly specify it.

does the update query in the procedure make a lock?

It does some form of locking. What is the value of autocommit? Do you explicitly use BEGIN and COMMIT? Is the table ENGINE=InnoDB? Please provide SHOW CREATE TABLE.

MySQL.slow_logs table for each query the lock _time shows 0.

The INSERT you show seems to be inserting the same row as the UPDATE. Maybe you need INSERT ... ON DUPLICATE KEY UPDATE ...?

Don't "hide an index column in a function"; instead of DATEDIFF(roomlogs.enterTime,NOW()) = 0, do

AND enterTime >= CURDATE()
AND enterTime  < CURDATE() + INTERVAL 1 DAY

This allows the index to be used more fully.

KEY `RoomLogIndex` (`roomId`),  Change to  (roomId, status)
KEY `RoomLogIDIndex` (`id`),    Remove, redundant with the PK

Buffer pool in only 97,517,568 -- make it more like 9G.