mysqlperformanceindexinginnodb

Mysql Query taking more time than expected

I have a simple MYSQL table containing a varchar column as PRIMARY KEY. Now, I'm querying on that column and the query is taking ~4 secs.

select entity_name from entity where entity_name in ('sunshine-int-001', 'sunshine-int-002');

When I run this query from mysql client it takes some milliseconds. But when there is some load on the server it takes more time.

Table

CREATE TABLE `entity` (
  `entity_name` varchar(400) NOT NULL,
  `entity_detail` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`entity_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Solution

  • Your query of

    select entity_name from entity where entity_name in ('sunshine-int-001', 'sunshine-int-002');

    is set to compare the primary key of each record with two strings. You state that sometimes it's quick, but when the server has high load, then it gets slow. So the difference between "quick" and "slow" stems from the server load. As a result, other applications or high load upon this one slows the query down.

    Now, when a write operation happens on your entity table, that locks down the table and read operations wait for the write to end. It's not surprising that your reads wait for the write to finish. Now, if you have many small writes, they can add up.

    So if you want to improve upon performance, then you could have two copies of the database, one that your application would use for reads and the other that your application would use for writes.

    Your reads would not have live data, but your database meant for reads would be periodically updated by the stuff your database meant for writes has. This way reads would only get slow while updating in a periodic manner, but not constantly. But this comes with the cost of your data not being live for the users.

    Whether it's good or bad for you is up to you to decide.