| Server IP : 185.86.78.101 / Your IP : 216.73.216.213 Web Server : Apache System : Linux 675867-vds-valikoshka1996.gmhost.pp.ua 5.4.0-150-generic #167-Ubuntu SMP Mon May 15 17:35:05 UTC 2023 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : OFF Directory : /www/server/mysql/mysql-test/r/ |
Upload File : |
drop table if exists t1;
CREATE TABLE t1 (
a int not null,
b int not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 order by b;
a b
1 1
35 2
30 4
2 5
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b int unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b tinyint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b tinyint unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b smallint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b smallint unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b mediumint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b mediumint unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b bigint unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b bigint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b bigint not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b float not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b double not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b double unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b float unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b double precision not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b double precision unsigned not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b decimal not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (2, 5);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b char(10) not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b > 0 order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b varchar(10) not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b > '0' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b varchar(10) not null,
primary key(a),
index (b(5)))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b > '0' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b varchar(10) binary not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b > '0' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b tinytext not null,
primary key(a),
index (b(10)))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b > '0' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b text not null,
primary key(a),
index (b(10)))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b > '0' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b mediumtext not null,
primary key(a),
index (b(10)))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b > '0' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b longtext not null,
primary key(a),
index (b(10)))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b > '0' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b enum('1','2', '4', '5') not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b >= '1' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b set('1','2', '4', '5') not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '1');
INSERT into t1 values (2, '5');
INSERT into t1 values (30, '4');
INSERT into t1 values (35, '2');
select * from t1 force index (b) where b >= '1' order by b;
a b
1 1
35 2
30 4
2 5
drop table t1;
CREATE TABLE t1 (
a int not null,
b date not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '2001-01-01');
INSERT into t1 values (2, '2005-01-01');
INSERT into t1 values (30, '2004-01-01');
INSERT into t1 values (35, '2002-01-01');
select * from t1 force index (b) where b > '2000-01-01' order by b;
a b
1 2001-01-01
35 2002-01-01
30 2004-01-01
2 2005-01-01
drop table t1;
CREATE TABLE t1 (
a int not null,
b datetime not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '2001-01-01 00:00:00');
INSERT into t1 values (2, '2005-01-01 00:00:00');
INSERT into t1 values (30, '2004-01-01 00:00:00');
INSERT into t1 values (35, '2002-01-01 00:00:00');
select * from t1 force index (b) where b > '2000-01-01 00:00:00' order by b;
a b
1 2001-01-01 00:00:00
35 2002-01-01 00:00:00
30 2004-01-01 00:00:00
2 2005-01-01 00:00:00
drop table t1;
CREATE TABLE t1 (
a int not null,
b timestamp not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '2001-01-01 00:00:00');
INSERT into t1 values (2, '2005-01-01 00:00:00');
INSERT into t1 values (30, '2004-01-01 00:00:00');
INSERT into t1 values (35, '2002-01-01 00:00:00');
select * from t1 force index (b) where b > '2000-01-01 00:00:00' order by b;
a b
1 2001-01-01 00:00:00
35 2002-01-01 00:00:00
30 2004-01-01 00:00:00
2 2005-01-01 00:00:00
drop table t1;
CREATE TABLE t1 (
a int not null,
b time not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, '01:00:00');
INSERT into t1 values (2, '05:00:00');
INSERT into t1 values (30, '04:00:00');
INSERT into t1 values (35, '02:00:00');
select * from t1 force index (b) where b > '00:00:00' order by b;
a b
1 01:00:00
35 02:00:00
30 04:00:00
2 05:00:00
drop table t1;
CREATE TABLE t1 (
a int not null,
b year not null,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 2001);
INSERT into t1 values (2, 2005);
INSERT into t1 values (30, 2004);
INSERT into t1 values (35, 2002);
select * from t1 force index (b) where b > 2000 order by b;
a b
1 2001
35 2002
30 2004
2 2005
drop table t1;
CREATE TABLE t1 (
a int not null,
b bit(5) not null,
c int,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, b'00001', NULL);
INSERT into t1 values (2, b'00101', 2);
INSERT into t1 values (30, b'00100', 2);
INSERT into t1 values (35, b'00010', NULL);
select a from t1 force index (b) where b > b'00000' order by b;
a
1
35
30
2
drop table t1;
CREATE TABLE t1 (
a int not null,
b bit(15) not null,
c int,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, b'000000000000001', NULL);
INSERT into t1 values (2, b'001010000000101', 2);
INSERT into t1 values (30, b'001000000000100', 2);
INSERT into t1 values (35, b'000100000000010', NULL);
select a from t1 force index (b) where b > b'000000000000000' order by b;
a
1
35
30
2
drop table t1;
CREATE TABLE t1 (
a int not null,
b int,
primary key(a),
index (b))
partition by range (a)
partitions 2
(partition x1 values less than (25),
partition x2 values less than (100));
INSERT into t1 values (1, 1);
INSERT into t1 values (5, NULL);
INSERT into t1 values (2, 4);
INSERT into t1 values (3, 3);
INSERT into t1 values (4, 5);
INSERT into t1 values (7, 1);
INSERT into t1 values (6, 6);
INSERT into t1 values (30, 4);
INSERT into t1 values (35, 2);
INSERT into t1 values (40, NULL);
select * from t1 force index (b) where b < 10 OR b IS NULL order by b;
a b
5 NULL
40 NULL
1 1
7 1
35 2
3 3
2 4
30 4
4 5
6 6
select * from t1 force index (b) where b < 10 ORDER BY b;
a b
1 1
7 1
35 2
3 3
2 4
30 4
4 5
6 6
select * from t1 force index (b) where b < 10 ORDER BY b DESC;
a b
6 6
4 5
30 4
2 4
3 3
35 2
7 1
1 1
drop table t1;
create table t1 (a int not null, b int, c varchar(20), key (a,b,c))
partition by range (b)
(partition p0 values less than (5),
partition p1 values less than (10));
INSERT into t1 values (1,1,'1'),(2,2,'2'),(1,3,'3'),(2,4,'4'),(1,5,'5');
INSERT into t1 values (2,6,'6'),(1,7,'7'),(2,8,'8'),(1,9,'9');
INSERT into t1 values (1, NULL, NULL), (2, NULL, '10');
select * from t1 where a = 1 order by a desc, b desc;
a b c
1 9 9
1 7 7
1 5 5
1 3 3
1 1 1
1 NULL NULL
select * from t1 where a = 1 order by b desc;
a b c
1 9 9
1 7 7
1 5 5
1 3 3
1 1 1
1 NULL NULL
drop table t1;