Why my simple searching query is too slow?

Sahasrar

8/15/23, 8:05 PM

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?

0 + 0

mysql

sql

database

query

database-performance

Score:0

Server

surfingonthenet

8/15/23, 8:52 PM

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.

0 + 0

Sahasrar

8/16/23, 7:33 PM

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?

surfingonthenet

8/17/23, 9:09 PM

@Sahasrar, InnoDB allows you to use CONSTRAINTS and handles ROW LOCK, which means InnoDB is a better choice if your table process a higher volume or UPDATEs and/or DELETEs. MyISAM allows concurrent INSERTs and SELECTs, which might be a better choice if your table handles load bulk INSERTS and very little UPDATEs or DELETEs

Sahasrar

8/17/23, 9:27 PM

This table si about search for nearest cities. So 99% of queries will be about reading. From your comment i think it is better for my table use MyISAM, am I wrong?

surfingonthenet

8/18/23, 5:45 PM