Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- table1.*,
- table2.*
- FROM
- Table1 AS table1
- LEFT JOIN
- Table2 AS table2
- USING
- (col1)
- LEFT JOIN
- Table3 as table3
- USING
- (col1)
- WHERE
- 3963.191 *
- ACOS(
- (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180))
- +
- (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
- ) <= 10
- AND
- table1.col1 != '1'
- AND
- table1.col2 LIKE 'A'
- AND
- (table1.col3 LIKE 'X' OR table1.col3 LIKE 'X-Y')
- AND
- (table2.col4 = 'Y' OR table2.col5 = 'Y')
- // Data Types of all columns in the query:
- // col1: int(11)
- // col2: char(1)
- // col3: varchar(3)
- // col4: char(1)
- // col5: char(1)
- // col6: int(11)
- // latitude: varchar(25)
- // longitude: varchar(25)
- // All 3 tables (table1, table2, and table3) are `MyISAM`.
- ORDER BY
- table1.col6 DESC
- id select_type table type possible_keys key key_len ref rows filtered Extra
- 1 SIMPLE table1 ALL PRIMARY,col2,col3 NULL NULL NULL 140101 72.61 Using where
- 1 SIMPLE table2 eq_ref PRIMARY,col4,col5 PRIMARY 4 table1.col1 1 100 Using where
- 1 SIMPLE table3 eq_ref PRIMARY PRIMARY 4 table1.col1 1 100 Using where
- id select_type table type possible_keys key key_len ref rows filtered Extra
- 1 SIMPLE table1 ALL PRIMARY,col2,col3 NULL NULL NULL 140101 72.61 Using where; Using filesort
- 1 SIMPLE table2 eq_ref PRIMARY,col4,col5 PRIMARY 4 table1.col1 1 100 Using where
- 1 SIMPLE table3 eq_ref PRIMARY PRIMARY 4 table1.col1 1 100 Using where
- SELECT STRAIGHT_JOIN
- T1.*,
- T2.*
- FROM
- Table1 AS T1
- JOIN Table2 AS T2
- ON T1.Col1 = T2.Col1
- AND ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' )
- JOIN Table3 as T3
- ON T1.Col1 = T3.Col1
- AND 3963.191
- * ACOS( (SIN(PI() * $usersLatitude / 180) * SIN(PI() * T3.latitude / 180))
- + ( COS(PI() * $usersLatitude / 180) * COS(PI() * T3.latitude / 180)
- * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
- )
- ) <= 10
- WHERE
- T1.Col2 LIKE 'A'
- AND ( T1.col3 LIKE 'X' OR T1.col3 LIKE 'X-Y')
- AND T1.Col1 != '1'
- ORDER BY
- T1.Col6
- // With the following composite indexes:
- // On Table 1, index on ( Col2, Col3, Col1, Col6 )
- // On Table 2, index on ( Col1, Col4, Col5 )
- // Remember, all individual columns are already indexed.
- CREATE TABLE `Table1` (
- `col1` int(11) unsigned NOT NULL AUTO_INCREMENT,
- `col100` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
- `col101` varchar(60) COLLATE utf8_bin DEFAULT NULL,
- `col102` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
- `col103` varchar(10) COLLATE utf8_bin DEFAULT '00000000',
- `col104` date NOT NULL,
- `col105` int(3) DEFAULT NULL,
- `col106` varchar(25) COLLATE utf8_bin DEFAULT NULL,
- `col107` varchar(20) COLLATE utf8_bin DEFAULT 'Blah',
- `col108` varchar(2) COLLATE utf8_bin DEFAULT 'No',
- `col109` varchar(15) COLLATE utf8_bin DEFAULT 'Blah',
- `col2` enum('A','B') COLLATE utf8_bin DEFAULT NULL,
- `col3` enum('A','B','A-B') COLLATE utf8_bin DEFAULT NULL,
- `col110` decimal(10,7) NOT NULL DEFAULT '0.0000000',
- `col111` decimal(10,7) NOT NULL DEFAULT '0.0000000',
- `col112` char(1) COLLATE utf8_bin DEFAULT 'N',
- `col113` char(1) COLLATE utf8_bin DEFAULT 'N',
- `col114` int(11) DEFAULT NULL,
- `col115` varchar(15) COLLATE utf8_bin DEFAULT 'Blah',
- `col6` int(11) DEFAULT NULL,
- `col117` varchar(45) COLLATE utf8_bin DEFAULT NULL,
- `col118` varchar(2) COLLATE utf8_bin NOT NULL,
- `col119` tinyint(2) NOT NULL,
- `col120` int(6) NOT NULL,
- `col121` varchar(7) COLLATE utf8_bin NOT NULL,
- `col122` varchar(6) COLLATE utf8_bin NOT NULL,
- `col123` char(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
- `col124` varchar(200) COLLATE utf8_bin NOT NULL,
- `col125` tinyint(4) NOT NULL,
- `col126` tinyint(1) NOT NULL,
- `col127` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
- `col128` tinyint(1) NOT NULL DEFAULT '0',
- `col129` smallint(5) unsigned NOT NULL,
- `col130` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
- `col131` int(11) NOT NULL,
- `col132` tinyint(1) NOT NULL,
- `col133` tinyint(1) NOT NULL,
- `col134` varchar(1) COLLATE utf8_bin NOT NULL,
- `col135` varchar(200) COLLATE utf8_bin NOT NULL,
- `col136` int(11) NOT NULL,
- `col137` int(10) unsigned NOT NULL,
- `col138` int(11) NOT NULL,
- `col139` tinyint(1) NOT NULL,
- `col140` tinyint(1) NOT NULL,
- `col141` tinyint(4) NOT NULL,
- `col142` varchar(25) COLLATE utf8_bin NOT NULL,
- `col143` varchar(25) COLLATE utf8_bin NOT NULL,
- `col144` tinyint(1) unsigned NOT NULL,
- `col145` tinyint(4) NOT NULL,
- PRIMARY KEY (`col1`),
- KEY `col2` (`col2`),
- KEY `col3` (`col3`),
- KEY `CompositeIndex0` (`col1`,`col2`,`col3`,`col6`),
- KEY `CompositeIndex1` (`col2`,`col3`,`col1`,`col6`),
- KEY `idx01` (`col1`,`col2`,`col3`)
- [19 other indexes that do not involve col1, col2, col3, or col6...]
- ) ENGINE=MyISAM AUTO_INCREMENT=160640 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
- //*******************************************************//
- CREATE TABLE `Table2` (
- `col1` int(11) unsigned NOT NULL DEFAULT '0',
- `col201` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'Blah',
- `col202` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'Blah',
- `col203` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col204` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col205` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col206` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col207` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col208` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col209` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col210` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col211` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col212` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col213` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col214` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col215` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col216` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col217` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col218` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col219` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
- `col220` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
- `col221` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
- `col222` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
- `col223` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
- `col224` varchar(45) COLLATE utf8_bin DEFAULT βBlahβ,
- `col225` varchar(255) COLLATE utf8_bin DEFAULT NULL,
- `col4` char(1) COLLATE utf8_bin DEFAULT 'A',
- `col226` char(1) COLLATE utf8_bin DEFAULT 'A',
- `col227` varchar(5) COLLATE utf8_bin DEFAULT 'Blah',
- `col228` char(1) COLLATE utf8_bin NOT NULL,
- `col229` text COLLATE utf8_bin,
- `col5` char(1) COLLATE utf8_bin DEFAULT 'A',
- `col230` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
- `col231` varchar(255) COLLATE utf8_bin DEFAULT NULL,
- `col232` varchar(255) COLLATE utf8_bin DEFAULT NULL,
- `col233` varchar(255) COLLATE utf8_bin DEFAULT NULL,
- PRIMARY KEY (`col1`),
- KEY `col4` (`col4`),
- KEY `col5` (`col5`),
- KEY `CompositeIndex1` (`col1`,`col4`,`col5`),
- [4 other indexes not involving col1, col4, col5...]
- FULLTEXT KEY `col220` (`col220`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
- //*******************************************************//
- CREATE TABLE `Table3` (
- `col1` int(11) unsigned NOT NULL DEFAULT '0',
- `col300` varchar(255) COLLATE utf8_bin DEFAULT NULL,
- `latitude` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '0',
- `longitude` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '0',
- `col301` int(11) DEFAULT NULL,
- `static2` float(18,16) DEFAULT '0.0000000000000000',
- `static3` float(18,16) DEFAULT '0.0000000000000000',
- PRIMARY KEY (`col1`),
- KEY `latitude` (`latitude`),
- KEY `longitude` (`longitude`),
- KEY `static2` (`static2`),
- KEY `static3` (`static3`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
- # The MySQL database server configuration file.
- [mysqld]
- open-files-limit = 20000
- thread-cache-size = 16
- table-open-cache = 2048
- table-definition-cache = 512
- query-cache-type = 1
- query-cache-size = 32M
- query-cache-limit = 1M
- sort-buffer-size = 1M
- read-buffer-size = 1M
- read-rnd-buffer-size = 8M
- join-buffer-size = 1M
- tmp-table-size = 64M
- max-heap-table-size = 64M
- back-log = 100
- max-connections = 200
- max-connect-errors = 10000
- max-allowed-packet = 16M
- interactive-timeout = 600
- wait-timeout = 180
- net_read_timeout = 30
- net_write_timeout = 30
- back_log = 128
- myisam-sort-buffer-size = 128M
- innodb-buffer-pool-size = 320M
- innodb-log-buffer-size = 4M
- innodb-log-file-size = 128M
- innodb-log-files-in-group = 2
- innodb-file-per-table = 1
- [mysqldump]
- max-allowed-packet = 16M
- id select_type table type possible_keys key key_len ref rows Extra
- 1 SIMPLE T1 ref PRIMARY,col2,col3,col1,CompositeIndex1,idx01 CompositeIndex1 2 const 92333 Using where; Using filesort
- 1 SIMPLE T3 eq_ref PRIMARY PRIMARY 4 T1.col1 1 Using where
- 1 SIMPLE T2 eq_ref PRIMARY,CompositeIndex1,idx_static1 PRIMARY 4 T1.col1 1 Using where
- SELECT STRAIGHT_JOIN T1 . * , T2 . *
- FROM Table3 AS T3
- JOIN Table2 AS T2 ON T3.col1 = T2.col1
- AND (
- T2.col4 = 'Y'
- OR T2.col5 = 'Y'
- )
- JOIN Table1 AS T1 ON T3.col1 = T1.col1
- AND 3963.191 * ACOS( (
- SIN( PI( ) * - 87.8819594 /180 ) * SIN( PI( ) * T3.latitude /180 ) ) + ( COS( PI( ) * - 87.8819594 /180 ) * COS( PI( ) * T3.latitude /180 ) * COS( PI( ) * T3.longitude /180 - PI( )* 37.1092162 /180 ) )
- ) <=10
- WHERE T1.col2 LIKE 'A'
- AND (
- T1.col3 LIKE 'X'
- OR T1.col3 LIKE 'X-Y'
- )
- AND T1.col1 != '1'
- ORDER BY T1.col6 DESC
- id select_type table type possible_keys key key_len ref rows filtered Extra
- 1 SIMPLE T3 ALL PRIMARY NULL NULL NULL 141923 100 Using where; Using temporary; Using filesort
- 1 SIMPLE T2 eq_ref PRIMARY,col4,col5,CompositeIndex1 PRIMARY 4 T3.col1 1 100 Using where
- 1 SIMPLE T1 eq_ref PRIMARY,col2,col3,col1,CompositeIndex1,idx01 PRIMARY 4 T2.col1 1 100 Using where
- SIN(PI() * T3.latitude / 180)
- COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
- COS(PI() * T3.latitude / 180)
- ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' )
- alter table table2 add static1 bit default 0;
- alter table add index idx_static1(static1);
- update table2 t2 set static1=1 where ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' );
- alter table table3 add static2 float(18,16) default 0;
- update table3 set static2=SIN(PI() * T3.latitude / 180) where 1
- alter table table3 add static3 float(18,16) default 0;
- update table3 set static3 = COS(PI() * T3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180) where 1
- alter table table1 change col2 col2 enum('A','B','C');
- alter table table1 change col3 col3 enum('X','Y','X-Y');
- SELECT STRAIGHT_JOIN
- T1.*,
- T2.*
- FROM
- Table1 AS T1
- JOIN Table2 AS T2 ON T1.Col1 = T2.Col1
- JOIN Table3 as T3 ON T1.Col1 = T3.Col1
- WHERE static1=1 AND
- T1.Col2 = 'A'
- AND T1.col3 IN ( 'X', 'X-Y')
- AND T1.Col1 != 1
- AND ACOS(
- (
- $usersLatitude_sin_pi_fract180 * t3.static2
- + $usersLatitude_cos_pi_fract180 * t3.static3
- )
- ) <= 0,00252321929476 -- this's 10/3963.191
- ORDER BY T1.Col6
- t1.col2 = 'A'
- SELECT T1.*, T2.*
- FROM Table1 AS T1
- JOIN Table2 AS T2 ON ( T1.Col1 = T2.Col1 )
- JOIN Table3 as T3 ON T1.Col1 = T3.Col1
- WHERE
- ( T1.Col2 = 'A' collate utf8_bin AND T1.col3 IN ( 'X' collate utf8_bin , 'X-Y' collate utf8_bin ) AND T1.Col1 != 1 )
- and T2.static1=1
- AND ACOS( ( 2.3 * T3.static2 + 1.2 * T3.static3 ) ) <= 0.00252321929476
- ORDER BY T1.Col6
- +----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
- | 1 | SIMPLE | T1 | ref | PRIMARY,col2,col3,CompositeIndex1 | CompositeIndex1 | 1 | const | 1 | 100.00 | Using where; Using filesort |
- | 1 | SIMPLE | T2 | eq_ref | PRIMARY,CompositeIndex1 | PRIMARY | 4 | testdb.T1.col1 | 1 | 100.00 | Using where |
- | 1 | SIMPLE | T3 | eq_ref | PRIMARY | PRIMARY | 4 | testdb.T1.col1 | 1 | 100.00 | Using where |
- +----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
- effort = num_rows*key_size/index_cardinality
- FROM table3
- ...
- WHERE (t3.latitude-0.15) < $usersLatitude AND $usersLatitude < t3.latitude+0.15
- AND t3.longitue - 0.15 < $usersLongitude AND $usersLongitude < t3.longitue + 0.15
- FROM table3 t3
- JOIN table3 t3bis ON t3.id=t3bis.id
- ...
- WHERE (t3.latitude-0.15) < $usersLatitude AND $usersLatitude < t3.latitude+0.15
- AND t3.longitue - 0.15 < $usersLongitude AND $usersLongitude < t3.longitue + 0.15
- AND
- 3963.191
- * ACOS( (SIN(PI() * $usersLatitude / 180) * SIN(PI() * t3bis.latitude / 180))
- + ( COS(PI() * $usersLatitude / 180) * COS(PI() * t3bis.latitude / 180)
- * COS(PI() * t3bis.longitude / 180 - PI() * 37.1092162 / 180)
- )
- ) <= 10
- degree * PI / 180 == radians(degree)
- ...
- FROM
- Table1 AS table1 USE INDEX (col6)
- LEFT JOIN
- Table2 AS table2
- ...
- SELECT * FROM
- {
- SELECT
- col1, col2, col3, col4, col5, col6
- FROM
- Table1 AS table1
- LEFT JOIN
- Table2 AS table2
- USING
- (col1)
- LEFT JOIN
- Table3 as table3
- USING
- (col1)
- WHERE
- 3963.191 *
- ACOS(
- (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180))
- +
- (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
- ) <= 10
- ) AS sub
- WHERE
- col1 != '1'
- AND
- col2 LIKE 'A'
- AND
- (col3 LIKE 'X' OR col3 LIKE 'X-Y')
- AND
- (col4 = 'Y' OR col5 = 'Y')
- ORDER BY
- col6 DESC
- id select_type table type possible_keys key key_len ref rows filtered Extra
- 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 43 100 Using where; Using filesort
- 2 DERIVED T3 ALL PRIMARY NULL NULL NULL 143153 100 Using where
- 2 DERIVED users eq_ref PRIMARY,col1,idx01 PRIMARY 4 T3.col1 1 100
- 2 DERIVED userProfile eq_ref PRIMARY,CompositeIndex1 PRIMARY 4 users.col1 1 100
- ALTER TABLE Table1 ADD INDEX col2_col6_ndx (col2,col6);
- SELECT
- table1.*,
- table2.*
- FROM
- (
- SELECT * FROM Table1
- WHERE col2='A' AND
- ORDER BY col6 DESC
- ) AS table1
- LEFT JOIN
- (
- SELECT * FROM Table2
- WHERE (col4='Y' OR col5='Y')
- ) AS table2
- USING
- (col1)
- LEFT JOIN
- Table3 as table3
- USING
- (col1)
- WHERE
- table1.col1 != '1' AND
- table1.col3 IN ('X','X-Y') AND
- 3963.191 *
- ACOS(
- (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180))
- +
- (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
- ) <= 10
- ;
- ALTER TABLE Table1 ADD INDEX col2613_ndx (col2,col6,col1,col3);
- ALTER TABLE Table2 ADD INDEX col4_col1_ndx (col4,col1);
- ALTER TABLE Table2 ADD INDEX col5_col1_ndx (col5,col1);
- SELECT
- table1.*,
- table2.*
- FROM
- (
- SELECT table1.col1,table3.latitude,table3.longitude
- FROM
- (
- SELECT col1 FROM Table1 WHERE col2='A' AND
- AND col3 IN ('X','X-Y') ORDER BY col6 DESC
- ) AS table1
- LEFT JOIN
- (
- SELECT col1 FROM Table2 WHERE col4='Y' UNION
- SELECT col1 FROM Table2 WHERE col5='Y'
- ) AS table2
- USING (col1)
- LEFT JOIN Table3 as table3 USING (col1)
- ) col1_keys
- LEFT JOIN Table1 table1 USING (col1)
- LEFT JOIN Table2 table2 USING (col1)
- WHERE
- 3963.191 *
- ACOS(
- (SIN(PI() * $usersLatitude / 180) * SIN(PI() * col1_keys.latitude / 180))
- +
- (COS(PI() * $usersLatitude / 180) * COS(PI() * col1_keys.latitude / 180)
- * COS(PI() * col1_keys.longitude / 180 - PI() * 37.1092162 / 180))
- ) <= 10
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement