Why my simple searching query is too slow?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply