473,324 Members | 2,179 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 1670
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: James | last post by:
My form and results are on one page. If I use : if ($Company) { $query = "Select Company, Contact From tblworking Where ID = $Company Order By Company ASC"; }
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
8
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
5
by: Steve Patrick | last post by:
Hi All You guys are my last hope, despite spending money on books and hours reading them I still can not achieve the results I need. I have designed a database in Access 2000 based on 1 table,...
4
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table...
6
by: Takeadoe | last post by:
Dear NG, Can someone assist me with writing the little code that is needed to run an update table query each time the database is opened? From what I've been able to glean from this group, the...
3
by: mcmahonb | last post by:
Hey people... I've been searching this forum for a few hours and even though this topic has been went over from many different angles; I cannot seem to figure out how to make things work on my...
4
by: n | last post by:
Hello! Here is a problem I hope you can point me to a solution. It Problem: A teacher needs to know which lesson to teach. A school has a curriculum with 26 lessons, A-Z. For a given class,...
47
by: Jo | last post by:
Hi there, I'm Jo and it's the first time I've posted here. I'm in process of creating a database at work and have come a little unstuck.....I'm a bit of a novice and wondered if anyone could...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.