Mysql selection when slowing down an indexed column on large tables

Question

I have two tables: A - 301 columns (the first named a1 int (11) Primary key, from 2nd to 301th - double (15.11)) and columns B-33 (1st number - b1 int (11) Unique Key, 2nd One - b2 varchar (100) Primary key, ..., 33rd - b33 int (11) MUL).

Both A and B have ~ 13.5 million entries.

My mysql query: for each pos value, with pos in the set (1, 1000, 2000, ..., 13500000) in thousands of 1000:

select A. *, b2, b5, b7, b8, b10, b13, b33 from A join B to a1 = b1, where b33> = pos and b33 <pos + 1000;

The request takes 1-5 seconds for the values ​​b33 <= 600,000. After that, the request starts from 20-30 seconds. When b33> = 8,000,000, the request starts from 60-70 seconds. I can not understand why the slowdown occurs. b33 is indexed, and joining occurs by a key that is defined as primary in one table and unique in another. Is there a workaround for this? This really hinders the speed of the code, and I will have to split tables A and B into several smaller ones if nothing works. I really hope I don’t need to do this! Please, help!

EDIT: Here is o / p EXPLAIN -

************* 1. *************
         id: 1
select_type:
      : B
       :
possible_keys: b1, b33
        : b33
    key_len: 4
        ref: NULL
       : 981
      :
************* 2. *************
         id: 1
select_type:
      : A
       : eq_ref
possible_keys:
         : PRIMARY
    key_len: 4
        ref: DBName.B.b1
       : 1
      :
2 (0,00 )

Answer 1

, -, ?

, ( , ?):

mysqlcheck --check --analyze --auto-repair --all-databases --silent

mysqlcheck , , .

InnoDB, innodb_buffer_pool_size ( , ). , (, , ), , 8 .

, , OPTIMIZE TABLE.

Answer 2

MySQL ( -!) , , . -, b33 ? , , ? -, , 13500? - :

select A.*, b2, b5, b7, b8, b10, b13, b33, (b33 - 1 DIV 1000) the_group
from A join B on a1=b1 

-, , MySQL , inlinew :

select A.*, b2, b5, b7, b8, b10, b13, b33 
from A join (select b1,b2, b5, b7, b8, b10, b13, b33 
             from B b33 >= pos and b33 < pos+1000) B_NEW 
     on a1=b1 ;

( ), , .

!

Answer 3

...

select A.*, b2, b5, b7, b8, b10, b13, b33 
  from A join B 
  on a1=b1 
  where b33 BETWEEN pos AND pos+999;

Answer 4

, B? (, b33 ):

SHOW INDEXES FROM B;

, B?

select b2, b5, b7, b8, b10, b13, b33 from B where b33 >= pos and b33 < pos+1000;

SHOW CREATE TABLE, b33 ( NULL)

MyISAM InnoDB ? ( SHOW CREATE TABLE).

Answer 5

.

, :

SET profiling=1;

select A.*, b2, b5, b7, b8, b10, b13, b33 from A join B on a1=b1 where b33 >= 0 and b33 < 1000;
SHOW PROFILE;

select A.*, b2, b5, b7, b8, b10, b13, b33 from A join B on a1=b1 where b33 >= 1000000 and b33 < 1001000;
SHOW PROFILE;


SET profiling=0;

, - , 600k , .

Answer 6

QUERY!!!

:

select A.*, b2, b5, b7, b8, b10, b13, b33
from A join B on a1=b1 where b33 >= pos and b33 < pos+1000;

:

SELECT
    AAA.*,b2,b5,b7,b8,b10,b13,b33
FROM
    A AAA INNER JOIN
    (
        select
            A.a1,b2,b5,b7,b8,b10,b13,b33
        from
            A INNER JOIN
            (
               SELECT
                   b1,b2,b5,b7,b8,b10,b13,b33
               FROM B
               WHERE
                    b33 >= pos and
                    b33 < pos+1000
            ) BB
            ON A.a1=B.b1
    ) BBB
    USING (a1)
;

- . , BBB 1000 .

!!!

Answer 7

ayesha129p,

try moving the b33 constraints to a join clause. It seems that the optimizer applies only one of the limitations of b33 to pre-bundle.

select A.*, b2, b5, b7, b8, b10, b13, b33 from A join B
  on a1=b1 and b33 >= pos and b33 < pos+1000;

Therefore, the optimizer must use the b33 index and reduce the number of rows B to 1000 before attempting to connect.