How to speed up a search on the mysql table?

Question

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!

Do you have hyphens in the phone number field. Else consider changing it to integer rather than VARCHAR. INT fields are retrieved faster compared to VARCHAR fields. — PK20, Nov 09 '15 at 07:21
if phone is primary key there is no need for LIMIT 1. Try partioning in MySQL — undefined_variable, Nov 09 '15 at 07:28
I use partioning( phone is login, there can be anything not required numbers — Valeriy Chupurnov, Nov 09 '15 at 07:37
@PK20: And then the phone number `0555123456` comes along, and it's different from `555123456`. Now what? Or `+421900123456789`, which is not the same thing as `421900123456789`. — Piskvor left the building, Nov 09 '15 at 08:15

score 2 · Answer 1 · answered Nov 09 '15 at 07:23

2

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

answered Nov 09 '15 at 07:23

DevOps

438
6
21

I have index. It is PRIMARY filed – Valeriy Chupurnov Nov 09 '15 at 07:27
You can try by adding index in primary key and the other way is create a stored procedure. – DevOps Nov 09 '15 at 09:31

score 0 · Answer 2 · answered Nov 09 '15 at 07:27

0

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.

answered Nov 09 '15 at 07:27

Pini Cheyni

5,073
2
40
58

Primary field is a UNIQUE field, isn't it? – Valeriy Chupurnov Nov 09 '15 at 07:56
It should be ! But it is possible to set index which isn't unique – Pini Cheyni Nov 09 '15 at 08:02

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 09 '15 at 10:12

Genoud Magloire

555
6
15

How to speed up a search on the mysql table?

3 Answers3