Why my simple searching query is too slow?

Question

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 <= 25 ORDER BY distance ASC 

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?

Answer 1

For this query, you're supposed to use an index for city_start + distance

CREATE INDEX idx_citie_start_distance ON cities (city_start, distance); 

You may also create two indexes: city_start and another for distance. That should work fine as well.