0

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?

1 Answer 1

2

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)

1
  • It works, thank you. I also got a tip to change storage engine to MyISAM and query time is same than InnoDB with Index.... Is that the way too? or is it not a good way how to make DB for this solution?
    – Sahasrar
    Commented Apr 16, 2022 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.