CODEWITHSUNDEEP
mysqlmariadb
user510040
user510040

Reputation: 199

Slow SQL in MariaDB

MariaDB. Server version: 5.5.5-10.1.18-MariaDB MariaDB Server

There are 3 tables: 

1. City: 4,000 rows (has foreign key country_id to Country.id and index)
2. Street: 40,000 rows  (has foreign key city_id to City.id and index)
3. House: 4,000,000,000 rows (has foreign key to Street.id and index)

Following SQL never finished: 

select count(*) from House , Street  WHERE Street.city_id IN 
(SELECT id FROM City WHERE country_id=177) 
and Street.id=House.street_id;

But if in the nested SELECT instead the original code:

(SELECT id FROM City WHERE country_id=177)

I put

SELECT id FROM City WHERE id IN (4617,4618)

when it runs < 0.5 sec

Below is Explain plan for slow query

+------+-------------+-------+--------+----------------------------+------------------------+---------+------------------+------------+-------------+
| id   | select_type | table | type   | possible_keys               | key                   | key_len | ref              | rows       | Extra       |
+------+-------------+-------+--------+-----------------------------+-----------------------+---------+------------------+------------+-------------+
|    1 | PRIMARY     | House | index  | IDX_House_streetIndex       | IDX_House_streetIndex | 11      | NULL             | 4,000,000,000 | Using index |
|    1 | PRIMARY     | Street| eq_ref | PRIMARY,IDX_DF9A1AD51E5D0459| PRIMARY               | 4       | House.street_id  |          1 |             |
|    1 | PRIMARY     | City  | eq_ref | PRIMARY,IDX_784DD132166D1F9C| PRIMARY               | 4       | Street.city_id   |          1 | Using where |
+------+-------------+-------+--------+------------------------------------------+------------------------------------------+---------+———————

Upvotes: 0

Views: 132

Answers (2)

Rick James
Rick James

Reputation: 142296

Don't use IN ( SELECT ... ); it usually performs poorly. Instead, use the equivalent JOIN.

select  count(*)
    FROM  City
    JOIN  Street  ON Street.city_id = City.id
    JOIN  House   ON House.street_id = Street.id
    WHERE  City.country_id=177

Then, you need these indexes:

City:    INDEX(country_id, id)
Street:  INDEX(city_id, id)
House:   INDEX(street_id)

Please, when asking this kind of question, provide SHOW CREATE TABLE.

4 billion houses? I doubt if there are that many in the world!

If theses suggestions are not sufficient, the reconsider whether the tables are "over-normalized".

Upvotes: 0

Tony
Tony

Reputation: 17647

Try this:

CREATE INDEX i_1 ON City(id);
CREATE INDEX i_2 ON City(country_id);

CREATE INDEX i_3 ON Street(id)
CREATE INDEX i_4 ON Street(city_id)

CREATE INDEX i_5 ON House(street_id);

Upvotes: 0

Related Questions