Geek asks and answers

MySQL, ordering across multiple columns from multiple tables

I have two tables:

Table of Artists (tbl_artist):

artist_id - primary key
artist_name - has index   


Table of Albums (tbl_album):

album_id - primary key
album_artist_id - foreign key, has index
album_name - has index too

The tables have many entries on the production server (artists - 60 thousand, albums - 250 thousand).

And on the index page there is a list of albums in increments of pagination = 50. Albums are sorted by ASC artist name, ASC album name. Thus, the query is simplified:

SELECT *
FROM tbl_artist, tbl_album
WHERE album_artist_id = artist_id
ORDER BY artist_name, album_name
LIMIT 0, 50

The request takes a very long time. This is probably due to ordering by columns from different tables. When I leave only 1 request, the request is executed immediately.

What can be done in such a situation? Many thanks.

Edit : Explain:

+----+-------------+---------------+--------+------------------+---------+---------+-----------------------------------+--------+---------------------------------+
| id | select_type | table         | type   | possible_keys    | key     | key_len | ref                               | rows   | Extra                           |
+----+-------------+---------------+--------+------------------+---------+---------+-----------------------------------+--------+---------------------------------+
|  1 | SIMPLE      | tbl_album     | ALL    | album_artist_id  | NULL    | NULL    | NULL                              | 254613 | Using temporary; Using filesort |
|  1 | SIMPLE      | tbl_artist    | eq_ref | PRIMARY          | PRIMARY | 4       | db.tbl_album.album_artist_id      |      1 |                                 |
+----+-------------+---------------+--------+------------------+---------+---------+-----------------------------------+--------+---------------------------------+

explain with STRAIGHT_JOIN

+----+-------------+---------------+------+-----------------+-----------------+---------+------------------------------------+-------+---------------------------------+
| id | select_type | table         | type | possible_keys   | key             | key_len | ref                                | rows  | Extra                           |
+----+-------------+---------------+------+-----------------+-----------------+---------+------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | tbl_artist    | ALL  | PRIMARY         | NULL            | NULL    | NULL                               | 57553 | Using temporary; Using filesort |
|  1 | SIMPLE      | tbl_album     | ref  | album_artist_id | album_artist_id | 4       | db.tbl_artist.artist_id            |     5 |                                 |
+----+-------------+---------------+------+-----------------+-----------------+---------+------------------------------------+-------+---------------------------------+
+3
source share
5 answers

Try changing the index from to (album_artist_id)to index at (album_artist_id, album_name).

+1

(artist_name, artist_id), (album_artist_id, album_name). , artist_id album_artist_id, (artist_name, album_name) .

, : ORDER BY artist_name, artist_id, album_name. , artist_name, , , , . .

artist_name album_name , , , - , , .

+1

order by.

, , EXPLAIN. :

explain select * from your_table order by col1, col2
0

The main thing you need to pay attention to if you do not have a complete where clause allowed by the index in column order is how many rows you need to scan to resolve the order. If you only check 50 lines to ensure 10 lines of the result set you get in decent form, but if you are 5000, you may need to rethink your indexing.

Secondly, you can increase sort_buffer_size large

0
source

More articles:


All Articles