| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-10-04, 05:37
|
|
Registered User
|
|
Join Date: Sep 2003
Location: St.Petersburg, Russia
Posts: 19
|
|
|
Optimising a query
|
|
Hi!
I have a query that executes extemely slow.
My database structure is:
artists:
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?
|
|
07-10-04, 07:50
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
add indexes for the columns used in the joins
each defined primary key already has one, so add them for the foreign keys
|
|
07-11-04, 03:20
|
|
Registered User
|
|
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 03:30.
|
07-11-04, 07:42
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
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
|
|
07-11-04, 07:53
|
|
Registered User
|
|
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?
|
|
07-11-04, 08:00
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
add indexes for the sort columns
either that, or don't bother showing the results sorted 
|
|
08-15-04, 06:06
|
|
Registered User
|
|
Join Date: Sep 2003
Location: St.Petersburg, Russia
Posts: 19
|
|
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.
|
|
08-15-04, 07:31
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
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
|
|
08-15-04, 10:38
|
|
Registered User
|
|
Join Date: Sep 2003
Location: St.Petersburg, Russia
Posts: 19
|
|
Thank you for the input, but the query takes about 50 seconds now.
|
|
08-15-04, 14:36
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
wow
that bites the big one, doesn't it
i'm sorry, someone else is going to have to step in here
|
|
08-16-04, 11:56
|
|
Registered User
|
|
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?
|
|
08-16-04, 16:14
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Optimising a query
