CODEWITHSUNDEEP
mysqljoinsql-order-by
kasitan
kasitan

Reputation: 509

MySQL, order by 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

Tables have a lot of records on production server (artists - 60k, albums - 250k).

And on index page there is a list of albums, with pagination step = 50. Albums are sorted by artist_name ASC, album_name ASC. So the simplified query is following:

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

Query is executing very long. Probably it's because of ordering by columns from different tables. When I leave only 1 ordering - query is executing immediately.

What is possible to do in such 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 |                                 |
+----+-------------+---------------+------+-----------------+-----------------+---------+------------------------------------+-------+---------------------------------+

Upvotes: 7

Views: 8232

Answers (5)

Joe
Joe

Reputation: 51

You need an index of (artist_name, artist_id) and then (album_artist_id, album_name). The reason is because your join is between artist_id and album_artist_id, so it has to perform the same join with the indexes to produce a final need index of (artist_name, album_name) for the sort.

You then need to change your order by to be: ORDER BY artist_name, artist_id, album_name. This is because there could be two artist_name's that are the same, and this will cause it to order not as you are expecting. Also it will prevent it from using an index.

Using just indexes on artist_name, and album_name doesn't provide enough information to produce that sort, all you have is an ordered list of names with nothing to indicate how they connect to the other table.

Upvotes: 5

Moyed Ansari
Moyed Ansari

Reputation: 8461

The main thing to watch for, if you do not have full where clause resolved by index on order by column, is how many rows you need to scan to resolve order by . If only 50 rows are examined to provide 10 rows of result set you’re in decent shape but if it is 5000 you might need to rethink your indexing.

Second thing you can do , is to increase the sort_buffer_size large

Upvotes: 1

Hammerite
Hammerite

Reputation: 22340

Try changing the index on (album_artist_id) to an index on (album_artist_id, album_name).

Upvotes: 1

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

Adding indexes on columns that you sort by would be a fair shout. It will, yes, take more space in the server, but the execution would be at it's best. Read more about indexes: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html or http://www.tizag.com/mysqlTutorial/mysql-index.php

Upvotes: 0

juergen d
juergen d

Reputation: 204746

Add an index on the columns you order by.

if you want to know why your query takes so long then have a look at EXPLAIN. Example:

explain select * from your_table order by col1, col2

Upvotes: 0

Related Questions