CODEWITHSUNDEEP
mysqlsql
Valeriy Chupurnov
Valeriy Chupurnov

Reputation: 31

How to speed up a search on the mysql table?

I have MySQL table

CREATE TABLE IF NOT EXISTS `users` (
 `IDClient` int(11) NOT NULL,
 `Phone` varchar(15) NOT NULL,
 `passw` varchar(32) NOT NULL,
 `ip` varchar(50) DEFAULT NULL COMMENT 'IP адрес'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (Phone)
 PARTITIONS 10 */;
ALTER TABLE `users`
 ADD PRIMARY KEY (`Phone`),
 ADD KEY `IDClient` (`IDClient`),
 ADD KEY `ip` (`ip`);

It about 3 million records. When the site is a little loaded, sql request

SELECT * FROM users WHERE phone='9123456789' LIMIT 1

takes about 0.03 seconds, but when the site more loaded this time can reach up to 5 seconds. Who in the subject, can have any advice? Even 0.03 seconds, i think, much, for such a trivial request.

explain SELECT * FROM `users` where Phone="9123456789" limit 1

id  select_type table   type  possible_keys key     key_len ref     rows    Extra   
1   SIMPLE      users   const PRIMARY       PRIMARY 47      const   1       NULL

Maybe it helps the real partition table on 2,5,10 ... tables? Thank you!

Upvotes: 3

Views: 153

Answers (3)

Genoud Magloire
Genoud Magloire

Reputation: 555

What Engine are you using? MyIsam is more fast for read operations and fulltext search. See this discussion. When to use MyIsam and Innodb

EDIT: I noticed that you specified the engine on the create table query. So you should consider using MyIsam engine instead of innodb.

Upvotes: 0

Pini Cheyni
Pini Cheyni

Reputation: 5437

You have to set at least one unique field otherwise all the field combined will be treated as key and this makes it much slower.

Upvotes: 0

DevOps
DevOps

Reputation: 438

Add index to the column used in where clause , it will surely enhance processing speed.

Upvotes: 2

Related Questions