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 ?

[Tony]

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

[Bill Karwin]

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.

[Rich R]

"Jim" <ir******@NOSPA Mhotmail.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

[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_arti st` (`title`,`artis t_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_nam e` (`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_title s` (
`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 ?

[Rich R]

"Jim" <ir******@NOSPA Mhotmail.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

[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

[Jim]

Rich
I sent you by email a sample database to test