469,945 Members | 2,337 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

help on a query

Jim
I need help on a query.
There is a common titles database for several radio stations
Some titles are enabled and some are not enabled for each station.

Example of some tables:

Titles Stations Station_Titles
------ -------- -------------
id id Station_ID
Title StationName Title_ID
Enabled

I want to display all the titles of stationID = 1 and Enabled = "Y"

(In the query there are some other joins for album, artist etc.)
The problem is that the query I use takes more than a minute to get the
results for 1000 titles. Whithout this join it takes only 2 seconds to
get 2000 records.

this query takes .5 sec to execute :

select a.name as artist_name, a.id as artist_id, b.title as album_title,
b.id as album_id,
t.* from category_titles ct
inner join titles t on ct.title_id = t.id
left join artists a on t.artist_id = a.id
left join albums b on t.album_id = b.id
where ct.category_id is not null
group by a.name, t.title

but the complete Query :

select a.name as artist_name, a.id as artist_id, b.title as
album_title,
b.id as album_id,
t.* from category_titles ct
inner join titles t on ct.title_id = t.id
left join artists a on t.artist_id = a.id
left join albums b on t.album_id = b.id
inner join station_titles st on t.id = st.title_id and st.station_id =
1 and st.enabled = "Y"
where ct.category_id is not null
group by a.name, t.title

takes 1 minute for 1000 records

any suggestions on how to improve performance of the query ?
Jul 20 '05 #1
7 1537
Jim wrote:
I need help on a query.
There is a common titles database for several radio stations
Some titles are enabled and some are not enabled for each station.

Example of some tables:

Titles Stations Station_Titles
------ -------- -------------
id id Station_ID
Title StationName Title_ID
Enabled

I want to display all the titles of stationID = 1 and Enabled = "Y"

(In the query there are some other joins for album, artist etc.)
The problem is that the query I use takes more than a minute to get the
results for 1000 titles. Whithout this join it takes only 2 seconds to
get 2000 records.

this query takes .5 sec to execute :

select a.name as artist_name, a.id as artist_id, b.title as album_title,
b.id as album_id,
t.* from category_titles ct
inner join titles t on ct.title_id = t.id
left join artists a on t.artist_id = a.id
left join albums b on t.album_id = b.id
where ct.category_id is not null
group by a.name, t.title

but the complete Query :

select a.name as artist_name, a.id as artist_id, b.title as
album_title,
b.id as album_id,
t.* from category_titles ct
inner join titles t on ct.title_id = t.id
left join artists a on t.artist_id = a.id
left join albums b on t.album_id = b.id
inner join station_titles st on t.id = st.title_id and st.station_id =
1 and st.enabled = "Y"
where ct.category_id is not null
group by a.name, t.title

takes 1 minute for 1000 records

any suggestions on how to improve performance of the query ?


You don't mention the schema - are you using indexes?
http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html
http://dev.mysql.com/doc/mysql/en/LE...imization.html

and

http://dev.mysql.com/doc/mysql/en/Query_Speed.html
--
Tony
Jul 20 '05 #2
Jim wrote:
select a.name as artist_name, a.id as artist_id, b.title as
album_title,
b.id as album_id,
t.* from category_titles ct
inner join titles t on ct.title_id = t.id
left join artists a on t.artist_id = a.id
left join albums b on t.album_id = b.id
inner join station_titles st on t.id = st.title_id and st.station_id =
1 and st.enabled = "Y"
You should make sure that an index exists for each of the columns,
st.title_id, st.station_id and st.enabled.

Also, it's a bit confusing that you are putting row restriction
conditions in your 'ON' clause here, instead of putting them in the
'WHERE' clause below. The result is the same in the case of your query,
but it's worthwhile to develop a distinction between join conditions and
row restriction conditions, because sometimes it matters (for example,
in outer joins).
where ct.category_id is not null
group by a.name, t.title
For what it's worth, I think you're using 'group by' when you mean
'order by'. They achieve the same thing in this case (sorting the
result), but only by coincedence.
takes 1 minute for 1000 records

any suggestions on how to improve performance of the query ?


The usual easy suggestions are to examine the output of EXPLAIN for your
query, and make sure you have indexes on the columns that you're using
for sorting, join conditions, and row restriction conditions.

Regards,
Bill K.
Jul 20 '05 #3

"Jim" <ir******@NOSPAMhotmail.com> wrote in message
news:1104249219.19435@athnrd02...
I need help on a query.
There is a common titles database for several radio stations
Some titles are enabled and some are not enabled for each station.

Example of some tables:

Titles Stations Station_Titles
------ -------- -------------
id id Station_ID
Title StationName Title_ID
Enabled

I want to display all the titles of stationID = 1 and Enabled = "Y"

(In the query there are some other joins for album, artist etc.)
The problem is that the query I use takes more than a minute to get the
results for 1000 titles. Whithout this join it takes only 2 seconds to
get 2000 records.

this query takes .5 sec to execute :

select a.name as artist_name, a.id as artist_id, b.title as album_title,
b.id as album_id,
t.* from category_titles ct
inner join titles t on ct.title_id = t.id
left join artists a on t.artist_id = a.id
left join albums b on t.album_id = b.id
where ct.category_id is not null
group by a.name, t.title

but the complete Query :

select a.name as artist_name, a.id as artist_id, b.title as
album_title,
b.id as album_id,
t.* from category_titles ct
inner join titles t on ct.title_id = t.id
left join artists a on t.artist_id = a.id
left join albums b on t.album_id = b.id
inner join station_titles st on t.id = st.title_id and st.station_id =
1 and st.enabled = "Y"
where ct.category_id is not null
group by a.name, t.title

takes 1 minute for 1000 records

any suggestions on how to improve performance of the query ?


I'd be happy to give it a shot. Please post your create table statements and
if possible some sample data.

Regards,
Rich
Jul 20 '05 #4
Jim
In fact in table "station_titles" I do not use indexes :(

the create statements are as follows :

CREATE TABLE `albums` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`artist_id` int(11) NOT NULL default 0,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_album_artist` (`title`,`artist_id`)
) TYPE=MyISAM;

CREATE TABLE `artists` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`repeat` int(11) NOT NULL default 0,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_artist_name` (`name`)
) TYPE=MyISAM;

CREATE TABLE `titles` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
/* some other fields */
`full_path` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

CREATE TABLE `category_titles` (
`id` int(11) NOT NULL auto_increment,
`category_id` int(11) NOT NULL default 0,
`title_id` int(11) NOT NULL default 0,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_cat_title` (`category_id`,`title_id`)
) TYPE=MyISAM;

CREATE TABLE `station_titles` (
`station_id` int(11) NOT NULL default 0,
`title_id` int(11) NOT NULL default 0,
`enabled` enum('Y','N') NOT NULL default 'Y'
) TYPE=MyISAM;

what other indexes do you think I should create ?
Jul 20 '05 #5

"Jim" <ir******@NOSPAMhotmail.com> wrote in message
news:1104249219.19435@athnrd02...
I need help on a query.
There is a common titles database for several radio stations
Some titles are enabled and some are not enabled for each station.<


Hi Jim,
I've recreated your database and have loaded sample data, However, you
queries will not work because table Titles is missing some columns (i.e
artist_id?). What other columns are in this table? Also, what is the
relationship between Titles and Albums?

Regards,
Rich
Jul 20 '05 #6
Jim
You are right. I forgot some fields. Here you are:

CREATE TABLE `titles` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`artist_id` int(11) NOT NULL default 0,
`album_id` int(11) NOT NULL default 0,
`tempo_id` int(11) NOT NULL default 0,
`mood_id` int(11) NOT NULL default 0,
`Rating_id` int(11) NOT NULL default 0,
`full_path` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

There are some other tables for Mood, Tempo, Rating that are all like :
CREATE TABLE `moods` (
`id` int(11) NOT NULL,
`info` varchar(255) default NULL,
`Color` int(11) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM;

The relation between Albums and Titles is by field "album_id" I forgot
to include in my previous post.

Finaly I added an index to "station_titles" and the query is now fast
enough.

I 'll try to load about 50000 titles to database to test the
performance.

Thanks
Jul 20 '05 #7
Jim
Rich
I sent you by email a sample database to test
Jul 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by James | last post: by
9 posts views Thread by netpurpose | last post: by
9 posts views Thread by Dom Boyce | last post: by
5 posts views Thread by Steve Patrick | last post: by
6 posts views Thread by Takeadoe | last post: by
47 posts views Thread by Jo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.