MySQL performance problem

indexMySQLoptimizationperformancequery-performance

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server:

+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| tweetid     | varchar(255)   | NO   | PRI | NULL    |       |
| user_id     | varchar(255)   | NO   | PRI | NULL    |       |
| screen_name | varchar(255)   | NO   | PRI | NULL    |       |
| date        | datetime       | NO   | PRI | NULL    |       |
| json_src    | varchar(15000) | YES  |     | NULL    |       |
+-------------+----------------+------+-----+---------+-------+

When I select rows by user_id, query time ranges from a few seconds to more than 2 minutes, which is completely unacceptable. Initially I thought it was a memory problem, since I do not have this problem when indexes take up less space. So I made sure innodb_buffer_pool_size is higher than the total index size but that did not solve it. As total index size becomes large, some indexes are fast to get, others very slow. It does not make sense to me because all indexes should be fitting into memory.

Many posts seem to be about the same problem, and I tried all suggestions I could find, but nothing worked.

Any idea ?

Edit:

Here is the output of 'show create table name;'. As you can see, user_id is a primary key so it should be indexed.

CREATE TABLE `test` (
  `tweetid` varchar(255) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  `screen_name` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `json_src` varchar(15000) DEFAULT NULL,
  PRIMARY KEY (`tweetid`,`user_id`,`screen_name`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Edit 2:

The exact command is:

  select * from twittertweets where user_id=3678084920 limit 2;

With a select in front I get:

  | id | select_type | table         | type | possible_keys             
  | key  | key_len | ref  | rows    | Extra       |
  |  1 | SIMPLE      | twittertweets | ALL  | ix_twittertweets_user_id 
  | NULL | NULL    | NULL | 2231775 | Using where |

Since I created a new table, let me should you the output of 'show create table':

  | twittertweets | CREATE TABLE `twittertweets` (
  `tweetid` varchar(50) NOT NULL,
  `user_id` varchar(25) DEFAULT NULL,
  `screen_name` varchar(25) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `json_src` varchar(15000) DEFAULT NULL,
  PRIMARY KEY (`tweetid`),
  KEY `ix_twittertweets_user_id` (`user_id`),
  KEY `ix_twittertweets_screen_name` (`screen_name`),
  KEY `ix_twittertweets_date` (`date`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Best Answer

Using a varchar(255) as a 'one-size fits all' type is a bad idea, but that is another story, and not what you asked.

As @ypercube said, you have 2 options:

a) Create an additional index on user_id

ALTER TABLE `test` ADD INDEX `user_id` (`user_id`);

b) Reorder your PRIMARY key so it starts by user_id

ALTER TABLE `test` DROP PRIMARY KEY, 
ADD PRIMARY KEY (`user_id`, `tweetid`,`screen_name`,`date`);

This second option will take half the disk and memory space, but it may affect negatively queries filtering, for example, by tweetid only. You can read more about left-most prefix indexes and why your query was doing a full table scan here.

Related Question