Speed up mysql select query [closed]

Question

Am I missing any tricks to speed up this select query? the query is grabbing the location data as the users types it in, its auto filled.

$q = mysqli_real_escape_string(strtolower($_GET["q"]));

if (!$q) return;
    $sql = "SELECT us_city AS city, us_abv AS state, us_zip AS zip 
        FROM search_us
        WHERE us_zip LIKE '$q%' OR us_city 
        LIKE '$q%' GROUP BY us_abv, us_city, us_zip LIMIT 15";
    $places = mysqli_query($sql);
    while($place = mysqli_fetch_array($places)) {
    echo "$place[city], $place[state], $place[zip]\n"; 
}

Answer 1

OR is often handled poorly by the optimizer, because only one index is chosen, so only one half of the OR can benefit from an index-based search.

Splitting the query into two unioned queries usually improves performance, because a different index can be used for each half, so effectively two indexes can be used - one for each side of the OR.

Also, your query has an unnessessaru group by that should be removed.

Try this query (without irrelevant app code):

SELECT * FROM (
  SELECT
    us_city AS city,
    us_abv AS state,
    us_zip AS zip 
  FROM search_us
  WHERE us_city LIKE '$q%'
  UNION
  SELECT
    us_city AS city,
    us_abv AS state,
    us_zip AS zip 
  FROM search_us
  WHERE us_zip LIKE '$q%') x
LIMIT 15

The LIMIT clause must be applied to the result if the union.

Make sure you have separate indexes on us_city and us_zip.