#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    194
    Rep Power
    76

    Help making an sql query run faster


    I have a database which could potentially hold up to a maximum of 140,000 records and need to display all of these records which of course is very resource extensive

    also I have javascript and css and images which are making it even slower and all I have right now is 100 records and it takes up to 2 mins to load

    this is the sql I use

    I am using php for this as this is a webgame im making

    PHP Code:
    $sql "SELECT users.username as owner , cities.name as town ,cities.CorX as x,cities.CorY as y,cities.points as points, cities.picture as picture ".
    "FROM users INNER JOIN cities ON(users.username = cities.owner) ".
    "WHERE cities.CorX BETWEEN ".$startX." AND ".$endX." ".
    "AND cities.CorY BETWEEN ".$startY." AND ".$endY ." ";
    $sql mysql_query($sql) or die(mysql_error());

    while(
    $row mysql_fetch_array($sql)){
    $mapArray[$row['y']][$row['x']] = array(
    'owner' => $row['owner'],
    'townname'=> $row['town'],
    'points'=>$row['points'],
    'picture'=>$row['picture']
    );

    could anyone help me figure out how to get this query to run faster?



    you can see an example of this here but with this there's only 5 records in the database
    http://the-test.comoj.com/map/test_map4.php

    this doesn't work perfectly because for some odd reason only IE7+ is recognizing my z-index rules while chrome safari and firefox are ignoring it which makes the world map for my game usless
    Last edited by jack13580; November 5th, 2012 at 08:29 PM.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    MySQL has special spatial indexing extensions that are specifically intended to index this type of data; I highly recommend looking into those (google "MySQL spatial") because this type of query will be very difficult to optimize in any other way.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo