Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Location
    St.Petersburg, Russia
    Posts
    19

    Unanswered: Optimising a query

    Hi!

    I have a query that executes extemely slow.

    My database structure is:

    artists:
    Code:
     
    id
    name
    file
    songs:
    Code:
     
    id
    name
    file
    artist (Foreign key)
    tabs:
    Code:
     
    id
    type
    song (Foreign key)
    I have songs and tabs in separate tables because each song can have three tabs of different types.

    I hope it's clear and my query was:
    Code:
    SELECT tabs.type, songs.name AS song, artists.name AS artist,
    songs.file AS songFile, artists.file AS artistFile
    FROM tabs, songs, artists
    WHERE tabs.song = songs.id AND songs.artist = artists.id
    ORDER BY tabs.date DESC, songs.name
    LIMIT 0, 20
    then I tried
    Code:
    SELECT tabs.type, songs.name AS song, artists.name AS artist, songs.file AS songFile, artists.file AS artistFile
    FROM tabs
    INNER JOIN songs ON tabs.song = songs.id
    INNER JOIN artists ON songs.artist = artists.id
    ORDER BY tabs.date DESC, songs.name
    LIMIT 0, 20
    It was a bit faster, but still not enough.

    Also I figured out that if I removed 'ORDER BY' clause it executed fast, but I need ordering.

    Is there a way I can optimise the query or the database structure?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add indexes for the columns used in the joins

    each defined primary key already has one, so add them for the foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Location
    St.Petersburg, Russia
    Posts
    19
    So bacically I should run this query
    [code]
    ALTER TABLE songs DROP PRIMARY KEY, ADD PRIMARY KEY (id,artist);
    ALTER TABLE tabs DROP PRIMARY KEY, ADD PRIMARY KEY (id,song);
    [code]

    Right?

    I tried this and it began to work faster.
    But it still not enough. What else can I try to do?
    Last edited by Mike Borozdin; 07-11-04 at 04:30.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, no, PRIMARY KEY (id,artist) would be wrong

    the pk of artists is id

    the fk of songs is artist, and if these are myisam tables, you probably didn't even declare foreign keys, but never mind, as long as you declare an index on songs.artist

    similarly for the other keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    St.Petersburg, Russia
    Posts
    19
    But I figured out that the main problem is ordering, if I remove 'ORDER BY' clause, it loads much faster. Can I do something with that?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add indexes for the sort columns

    either that, or don't bother showing the results sorted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2003
    Location
    St.Petersburg, Russia
    Posts
    19

    Unhappy I just can't figure it out!

    Hi!

    I tried many things but still can't speed up the queries.

    First of all, I have MySQL 3.23.58 on a remote server.

    I changed my.cnf, so its variables are now:

    Code:
    set-variable=sort_buffer=4M
    set-variable=myisam_sort_buffer=2M
    set-variable=record_rnd_buffer=4M
    set-variable=key_buffer_size=64M
    set-variable=join_buffer_size=4M
    The server has 512 MB RAM.

    And for example, let's take this query:

    Code:
    SELECT artists.name AS artist, songs.name AS song, tabs.type,
    artists.file AS artistFile, songs.file AS songFile
    FROM tabs
    INNER JOIN songs ON tabs.song = songs.id
    INNER JOIN artists ON songs.artist = artists.id
    ORDER BY tabs.views DESC
    LIMIT 0, 10
    This query takes about 5 seconds.

    By the way, as for the data amount:

    Artists table: 7,330 rows
    Songs table: 49,667 rows
    Tabs table: 65,577 rows

    I was told to create indexed for foreign keys and columns used in sorting, I did so, but it didn't help (by the way, I did the same on my local machine with MySQL 4 and it really helped, but not on the remote machine). However, I did a thing that might look stupid add several columns to PRIMARY KEY:

    Code:
    tabs PRIMARY KEY: id, song, type
    What else shall I do?

    If I didn't provide enough information, I can you give you more details.

    Thanks in advance.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the primary key of tabs should be id

    the primary key of songs should be id

    the primary key of artists should be id

    now for the hard part

    there should be a separate index on tabs.song

    there should be a separate index on songs.artist

    there should be a separate index on tabs.views
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2003
    Location
    St.Petersburg, Russia
    Posts
    19
    Thank you for the input, but the query takes about 50 seconds now.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow

    that bites the big one, doesn't it

    i'm sorry, someone else is going to have to step in here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2003
    Location
    St.Petersburg, Russia
    Posts
    19
    Quote Originally Posted by r937
    wow

    that bites the big one, doesn't it

    i'm sorry, someone else is going to have to step in here
    Yeah, that's why I used multiple columns primary keys, even if it looked stupid. But, Rudy, have you got any ideas?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is going to sound like an excuse, but no, i don't

    if you've got indexes on your foreign keys, and on the columns used in searches in the WHERE clause (and of course on the primary keys, but they have indexes by default), then i'm not sure why it's so slow
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •