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 ? 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
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.
"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
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 ?
"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
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
Rich
I sent you by email a sample database to test This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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";
}
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |