V
V
Valery Chupurnov2015-11-09 09:53:24
MySQL
Valery Chupurnov, 2015-11-09 09:53:24

How to speed up table lookup?

There is a 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 has about 3 million records. When the site is not loaded search
SELECT * FROM users WHERE phone='9123456789' LIMIT 1

takes about 0.03 seconds, but when the site is reloading this time can reach up to 5 seconds.
Who in the subject, can you advise something? After all, even 0.03 seconds, IMHO, is a lot for such a banal request.
UPD. MySQL server
Server: Localhost via UNIX socket
Server type: Percona Server Server
version: 5.6.26-74.0-log - Percona Server (GPL), Release 74.0, Revision 32f8dfd
Explain
id     select_type  table     type          possible_keys       key             key_len          ref         rows     Extra
1      SIMPLE        users    range         PRIMARY             PRIMARY       47               NULL     1 	 NULL

Maybe a real partition of the table into 2,5,10 tables will help?

Answer the question

In order to leave comments, you need to log in

4 answer(s)
P
protven, 2015-11-09
@protven

Use caching. Memkeshed or radish will save the father of Russian democracy. And so, well, you can tune your muscle "according to the photograph", but there is zero sense in this.

A
Andrew, 2015-11-09
@drevil

What is the server doing during prime time?
big iowait?
What is the type of the Phone field?
line? And you need a number.

M
Max, 2015-11-09
@MaxDukov

Using where; Using filesort
filesort doesn't bother you?
An intimate question - why do you need Limit 1? Do you have several identical phones? If so, what is the point of such a request?

M
Marat, 2015-11-13
@Joysi75

`Phone` varchar(15) NOT NULL
If space on the server allows, it is better to change to
Since in this case it will be indexed by a fixed length key (in the case of int it is even better, it will be indexed by 4/8 bytes instead of 15 ) - accordingly, the search will be faster.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question