Why does my PHP/MYSQL request take up to 5mins to load? -

tl;dr - used join substitute inefficient in

i have program returns units listed 'covering' locations within distance of original locations.

that is, user searches region, distance , category. query returns offices cover of regions within searched distance of original region.

there 2 tables, 1 'publications/offices' , 1 regions covered each publication, linked index. in regions database, there entry each region publication covers.

the problem is, when run query takes 3mins return results?

i'm using shared cloud server, feel code inefficient. appreciated!

 <?php                      }                            $sql=mysql_query("select * publications ".$subwhereclause." , publications.entry_id in (( select regions_to_publications.pub_id  regions_to_publications ((acos(sin($lat * pi() / 180) * sin(lat * pi() / 180) + cos($lat * pi() / 180) * cos(lat * pi() / 180) * cos(($lon - lon) * pi() / 180)) * 180 / pi()) * 60 * 1.1515) *(1.6) < ".$_request["distance"].")) ") or die(mysql_error());                        ?>                     <?php while($row3= mysql_fetch_array($sql)) {?>                     <div class="result-entry-card">                      <p class="card-title"><?php echo $row3['entry_name'] ;?></p>                     <p class="card-cat"><?php echo $row3['entry_category'];?></p>                      <p><?php echo $row3['entry_phone'];?> - <a href="mailto:<?php echo $row3['entry_email'];?>"><?php echo $row3['entry_email'];?></a></p>                       <p><a href="http://<?php echo $row3['entry_website'];?>">email</a></p>                     </div>                     <?php } ?> 

any ideas why take long run through? i'm still learning!

update: ran explain on following code (substituting variables).

explain select *  publications publications.entry_category in (  ".newspapers." ) , publications.entry_id in ( (  select regions_to_publications.pub_id regions_to_publications ( ( acos( sin( - 33.8683 * pi( ) /180 ) * sin( lat * pi( ) /180 ) + cos( - 33.8683 * pi( ) /180 ) * cos( lat * pi( ) /180 ) * cos( ( 151.2086 - lon ) * pi( ) /180 ) ) *180 / pi( ) ) *60 * 1.1515 ) * ( 1.6 ) <  "1000" ) ) 

these results.

| id |select_type | table | type | possible_keys | key | key_len | ref | rows | |  | 1 | primary | publications | | null | null | null | null | 621 | using | | 2 | dependent subquery | regions_to_publications | | null | null | null | null | 84173 | using 

for wondering, looked through these results , redid search follows, utilizing join instead of in.

select *  publications join regions_to_publications on entry_id = pub_id ( ( acos( sin( - 33.8683 * pi( ) /180 ) * sin( regions_to_publications.lat * pi( ) /180 ) + cos( - 33.8683 * pi( ) /180 ) * cos( regions_to_publications.lat * pi( ) /180 ) * cos( ( 151.2086 - regions_to_publications.lon ) * pi( ) /180 ) ) *180 / pi( ) ) *60 * 1.1515 ) * ( 1.6 ) <  "1000"  ,  publications.entry_category in ( "radio" )  group publications.entry_id 

your sql contains major vulnerabilities $_request["distance"] , possibly $subwhereclause, depending on it's origins.

also, consider using pdo or mysqli because of this:

deprecated: mysql extension deprecated , removed in future.

anyways, answer question, try using explain , see mysql tells you. tell indexes being used , how many rows being scanned. should set on right track.

i recommend doing shell or phpmyadmin.

good luck!

p.s. - use explain add beginning of select statement.

explain select * table column=value 

edit - see have ran explain , posted results.

possible_keys , key pretty important (key same thing index). explain analyzing each query (since there subquery).

possible_keys being null mysql saying "i looked @ each index see if can use it, none of them worked". means had full table scan (rows number of rows needed scanned).

the subquery suffering lack of useable key. had scan 84173 rows. since query not simple, pretty intense scan on each row.

take @ indexes , figure out why mysql can't use them. reason recommend doing in phpmyadmin or shell can change query , see if index works. try , simplify down first , build up.