Why my simple searching query is too slow?



  • I don't know why searching in my "cities" table is so slow. My query looking for a table "cities" located about 25km from the city. I use this simple query and the database takes almost 20 seconds to return results.

    SELECT city_destination,distance FROM cities
         WHERE city_start='Wien'
           AND distance 

    Table engine is InnoDB. The table has approx. 7 millions of rows:

    +--------------------+-------------+------+-----+---------+----------------+
    | Field              | Type        | Null | Key | Default | Extra          |
    +--------------------+-------------+------+-----+---------+----------------+
    | id                 | int(11)     | NO   | PRI | NULL    | auto_increment |
    | id_of_start        | int(11)     | NO   |     | NULL    |                |
    | id_of_destination  | int(11)     | NO   |     | NULL    |                |
    | city_start         | text        | NO   |     | NULL    |                |
    | city_destination   | text        | NO   |     | NULL    |                |
    | distance           | double      | NO   |     | NULL    |                |
    +--------------------+-------------+------+-----+---------+----------------+
    

    Can anyone advise me how to optimize a database or query?



  • Just guessing what's happening as you've not provided a query plan or the indexes you've created.

    Assuming you expect this query to return relatively few rows.

    create index my_new_index on cities (city_start, distance );
    

    Order of the columns in the index is important, you only have an equality filter on city_start so this should come before distance otherwise it can't be used as part of the index selectivity (see https://ctandrewsayer.wordpress.com/2017/03/24/the-golden-rule-of-indexing/ for more info)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2