How to make ORDER BY in JOIN query faster? Nothing I have tried has worked

Question

I have the following JOIN query:

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`.

It executes in under 0.15 seconds.

However, if I simply add:

ORDER BY 
    table1.col6 DESC 

It executes in over 3 seconds.

All columns in the query are indexed, including the table1.col6 used in the ORDER BY.

Here are the results of EXPLAIN EXTENDED WITHOUT ORDER BY:

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

And here are the results of EXPLAIN EXTENDED WITH ORDER BY:

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

What's strange is that I use ORDER BY DESC in several other queries on this site, and it doesn't slow it down anywhere near as much as with this particular query. There is something specific to this query that is causing it to significantly slow down with the ORDER BY.

I also did an ANALYZE TABLE on all 3 tables, and they all reported OK. I then replaced every LIKE in the query with = and it actually made the query WITHOUT the ORDER BY go from 0.2 seconds to 3 seconds. In other words, replacing LIKE with = makes the original query take just as long as adding ORDER BY! How is that possible, considering LIKE does more work than =? Perhaps therein lies the clue as to why the ORDER BY takes so long?

HERE'S WHAT I'VE TRIED SO FAR (UNSUCCESSFULLY):

1) Instead of SELECT table1.*, table2.*, I tried just SELECT table1.col1 and it still took over 3 seconds to complete.

2) I tried adding a composite index on col1, col2, col3, and col6 in Table1, but it did not improve the execution speed.

3) I tried this solution of making the query as a sub-query and then wrapping the ORDER BY outside it at the end, but it did not improve the execution speed.

4) I tried the following version of the query, but it did NOT improve anything, and actually made the query take over 3 seconds WITHOUT even the ORDER BY added to it (perhaps that provides yet another clue):

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.

...

How can I get this stubborn query to run fast WITH the ORDER BY? Or is that just not possible?

---

EDIT:

Results of SHOW CREATE TABLE for all 3 Tables:

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

---

EDIT 2:

Below is my MySQL Configuration File. Among other things, please notice how the sort-buffer-size is set to 1M. According to this, it should not be set above 256K or it can actually slow things down by "37x". Could that be part of the problem?

# 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

On a different matter, here are the RESULTS OF EXPLAIN EXTENDED ON LATEST QUERY FROM IVAN:

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

On a different matter, here is something VERY strange. The following version of the query WITH ORDER BY completes in just 0.2 seconds:

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

Basically, this version of the query does a FROM Table3 AS T3 and JOIN tables 1 and 2 whereas the original query does FROM Table1 AS T1 and JOIN tables 2 and 3.

Here is the EXPLAIN EXTENDED for the query above:

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

Notice how this query actually does BOTH a filesort and a temporary vs. just a filesort on the original and new queries from Ivan. How can that be 10x faster?

Even stranger, switching the order of the JOIN does not seem to improve neither the original query nor the newer queries from Ivan. Why is that?

Answer 1

Well, I suggest you some restyles of the query:

1. put in where conditions not join related, see 2nd query:

   AND ( T1.col3 LIKE 'X' OR T1.col3 LIKE 'X-Y')

2. avoid OR use IN

3. avoid like use =

   AND T1.col3 IN ( 'X' , 'X-Y')

4. avoid computation in where

create some new columns to store:

SIN(PI() * T3.latitude / 180)
COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
COS(PI() * T3.latitude / 180) 

5. pre-evaluate

   SIN(PI() * $usersLatitude / 180) COS(PI() * $usersLatitude / 180)

6. if all these "tricks" can't avoid file-sort force the indexes

mysql query index hint

FURTHER ADD

in order to remove:

( T2.Col4 = 'Y' OR T2.Col5 = 'Y' )

in this case you can't use IN, so create a new column that is the result of this expression.

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

If table1.col2 has few values

alter table table1 change col2 col2 enum('A','B','C');

If table1.col3 has few values

alter table table1 change col3 col3 enum('X','Y','X-Y');

Create an unique index for all the columns involved in where alter table add index idx01 (col1,col2,col3)

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

Your comment suggest me that you've a different collation in the query (col1 is latin1_swedish and col2 is utf8) or your connection use a different collation (your connection is utf-8 and you query a latin1_german column) so when you query:

t1.col2 = 'A'

Mysql has to convert from utf-8 to latin1 each value.

See also the collate section of mysql documentation.

A quick way is to convert all (column, table, server, connection, client) to the same collation singel byte will be better if you don't need utf-8.

Be carefull to my type error or syntax error I could done.

FURTHER ADD 2

I recreated the tables on a test DB and I fixed this columns: t1.col2, t2.col3 must not nullable, t1.col1 is primary and can't be null.

Index "t1.CompositeIndex1" should index only: col2, col3, col1; Index an "order by" column is unusefull or worst.

I create static1 and I create an index on t2.col1 and t2.static1 but with my 6 rows in DB is not used (see explain later). t2.static1 must not nullable too.

I also adapt the query to the collate of the columns:

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

Here follows the explain extended

+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
| 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                 |
+----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+ 

Is it the same for the colums: select_type, table, type, key, ref, filtered, Extra?

My optimization goals are: - fit the where conditions in few index - avoid calculations - avoid collate conversion - avoid OR - avoid NULLs in where condition

Now the bad news It seems that in the tables you've ~140K records, and query using order can imply the using of the filesort approach if the query involve a lot of rows, so the final answer can be increase the memsort buffer as suggest by @mavroprovato.

FURTHER ADD 3

To evauate the adequacy of key_buffer_size see on http://dba.stackexchange.com

FURTHER ADD 4

I think that only someone in Oracle can say exactly what happens, but I've my idea.

I think that this query is peculiar:

1. all tables (t1,t2,t3) are joinded by primary key
2. other conditions depends only by calcs (t3.colX )
3. some conditions depend only by index (t1.colX )

Because of 1 from_table_rows >= join1_table_rows >= join2_table_rows, so less rows return the from table quickest will be the 2 other JOINs

The optimizer to evaluate the effort will calc a similar equation:

effort = num_rows*key_size/index_cardinality

(index_cardinality is show by phpmyadmin next each index)

Because of 2 effort is >= num_rows

My query because of 3 the the table1 (from table) returns 92333 rows, table3 (join1_table) reduce to 1(!) row, table2 keep 1 row (effort ~ 3).

Your query because of 2 you should have an effort = 140000, but luckylly for you the calc return just 1 results so your query is extremelly quick

Demostration

In your query changing from "<=10" (in join condition) to "<=1000" or more you will see a exponential decrease of performances.

In my query changing from "<=10" (in join condition) to "<=1000" or more you will see a linear/logarithm decrease of performances.

FURTHER ADD 5

answer to the question: is sort-buffer-size too big?

standing on article, yes, try some tune, may be you can solve the problem

answer to the question: is impossible to do a quick query?

IMHO no, it's possible (even if sort-buffer-size don't resolve).

My idea is quite simple, and it can resume in this mot: "cirlce is nice but square is better".

At the moment the biggest cardinality is on the coordinates in table3, but because of formula no index is applicable. So, instead of search all points inside a radius, you can search all points inside a "square"

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

so you can create an index in (t3.latitude,t3.longitue).

0.15 degrees should be 10 miles. Of course you should fix the calcs near the day-change meridian and next to the poles

If you need strictly a radius you can re-join table3 with the radius formula (see example below) or if possible carry out(/elaborate) the formula until you can compare directly values with columns.

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 

FURTHER ADD 6

topic: compiled functions do it better

use of RADIANS() function

degree * PI / 180 == radians(degree)

Use of GIS extension of mysql

See this article about MySql GIS extension