Geek asks and answers

PHP MySQL pagination slow

My table

Field   Type    Null    Key Default Extra
id      int(11)     NO  PRI NULL    auto_increment
userid  int(11)     NO  MUL NULL     
title   varchar(50) YES     NULL     
hosting varchar(10) YES     NULL     
zipcode varchar(5)  YES     NULL     
lat     varchar(20) YES     NULL     
long    varchar(20) YES     NULL     
msg    varchar(1000)YES MUL NULL     
time    datetime    NO      NULL

This is a table. I simulated 500 thousand rows of data and accidentally deleted 270 thousand rows, leaving only 230 thousand. With an automatic increment of 500 thousand

Here are my indexes

Keyname Type    Unique  Packed  Field   Cardinality Collation   Null
PRIMARY BTREE   Yes No  id            232377    A       
info    BTREE   No  No  userid          2003    A       
lat                                    25819    A   YES
long                                   25819    A   YES
title                                  25819    A   YES
time                                   25819    A

With that in mind, here is my query:

SELECT * FROM postsWHERE long> - 118.13902802886 AND long<-118.08130797114 AND lat> 33.79987197114 AND lat<33.85759202886 ORDER BY id ASC LIMIT 0, 25

Showing lines 0 - 15 (total 16 requests, requests 1.5655 sec) [id: 32846 - 540342]

The request only brought me 1 page, but because he had to search all 230 thousand records, it still took 1.5 seconds.

Here is an explanation of the request:

id  select_type table   type    possible_keys   key     key_len ref rows    Extra
1   SIMPLE      posts   index   NULL            PRIMARY 4       NULL 25     Using where

So, even if I use where clauses to get only 16 results, I still get a slow query.

, , :

SELECT * FROM `posts` WHERE `long`>-118.2544681443 AND `long`<-117.9658678557 AND `lat`>33.6844318557 AND `lat`<33.9730321443 ORDER BY id ASC LIMIT 0, 25

0 - 24 ( 25 , 0.0849 .) [id: 691 - 29818]

20 , 483 - , 25.

SELECT * FROM `posts` WHERE `long`>-118.2544681443 AND `long`<-117.9658678557 AND `lat`>33.6844318557 AND `lat`<33.9730321443 ORDER BY id ASC LIMIT 475, 25

0 - 7 ( 8 , 1,5874 ) [id: 553198 - 559593]

.

: ? , , , . id timestamp, Id , .

1 2 3 4 5 6 7 8 ... [Last Page]
+5
7

. , , - . , , . , , , , , WERE, ORDER BY LIMIT

SELECT * FROM 
    (SELECT * from `posts` as `p` 
        WHERE 
           `p`.`long`>-119.2544681443 
           AND `p`.`long`<-117.9658678557 
           AND `p`.`lat`>32.6844318557 A
           ND `p`.`lat`<34.9730321443  

    ) as posttable 
    order by id desc 
    limit x,n

, :

id  select_type     table        type   possible_keys   key key_len ref     rows    Extra
1   PRIMARY         <derived2>   ALL    NULL            NULL NULL   NULL    3031    Using filesort
2   DERIVED         p            ALL    NULL            NULL NULL   NULL    232377  Using where

232k, "where" orderby limit 3031.

0 - 3030 ( 3,031, 0.1431 )

0

, , WHERE: , . , id , .

+2

, mysql [ ] 20000 , 30 . , WHERE.

0

.

, order by id, , , 200000 , id > $last_id limit 20, .

, "" , ( ). id offset + limit.

, , .

0

, . , , :

create index idx_posts_id on posts (`id` ASC);
create index idx_posts_id_timestamp on posts (`id` ASC, `timestamp` ASC);

, , mysql, A, LOT.

0

Mysql : blog mysqlPerformance:

LIMIT. , , , , LIMIT. , LIMIT , . LIMIT 1000,10, , , LIMIT 0,10. , 10 , . , 200 . , -, , DOS- , . , , .

In some cases, for example, if the results are static, it may make sense to precompute the results so that you can request them for line items. Thus, instead of querying with a LIMIT of 1000.10, you will have a WHERE position between 1000 and 1009, which has the same performance for any position (if indexed)

0
source

If you use AUTO INCREMENT, you can use:

SELECT * FROMmessage WHEREid >= 200000 ORDER BYidDESC LIMIT 200000 , 30

Thus, mysql will only have to cross lines above 200000.

0
source

More articles:


All Articles