David,
I *really* do not like to be critical. However, for the sake of the newer
DBAs out there I feel the need to discourage your suggestion.
Your idea would work but I would not recommend it under most circumstances.
Breaking normalization should only be used as a "tool of last resort" when
you are trying to optimize query response time. As you said yourself, it is
not the most flexible idea as implementing it would require design changes
to both the database and the application in order to deal with what should
be a "data-only" problem, adding a new "character" type to your list of
track characters. Sorry. :-(
Most respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
|---------+---------------------------->
| | "David Scott" |
| | <[EMAIL PROTECTED]|
| | o.uk> |
| | |
| | 07/12/2004 09:01 |
| | AM |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: "MySQL List" <[EMAIL PROTECTED]>, "Giulio" <[EMAIL PROTECTED]>
|
| cc:
|
| Fax to:
|
| Subject: Re: can you help me optimizing this query?
|
>--------------------------------------------------------------------------------------------------------------------------------|
What about having a column for each genre, so tracks would look like:
id, title, artist, gItalian, gInternational, g80, g70
the genres are of type "int" so 1 for yes 0 for no.
then just do SELECT * FROM tracks WHERE g80 = 1 AND gItalian = 1
Not the most dynamic of solutions, but as your genre list wont change that
often? should be ok.
--
Dave
----- Original Message -----
From: "Giulio" <[EMAIL PROTECTED]>
To: "MySQL List" <[EMAIL PROTECTED]>
Sent: Monday, July 12, 2004 1:50 PM
Subject: can you help me optimizing this query?
> Hi all,
>
> I have an audio tracks info table, let's call it Tracks;
>
> every Track can have one or more ' Character' ( it is not a genre, it
> is something like 'Italian' or 'International' or '80's' or 'evergreen'
> )
>
> so a track can be 'International' and 'Evergreen', or 'Italian' and
> '70's')
>
> I have three tables to handle this:
>
> Track
> Character
> Track_Character ( the intermediate table to handle the Track-Characters
> connection)
>
> Now, if I want to search all tracks, let's say by title, and have for
> every track its data and its characters codes, I'm doing something
> like:
>
> select track.*, track_character.id_character from track left join
> track_character on track.track_id = track_character.track id where
> track_title = '%something%' order by track_title
>
> this seems to work, but it is VERY VERY slow, and I'm working with only
> 4000-5000 track records, most of them having only one track_character
> record. It can last up to 120 seconds if I leave the title blank,
> resulting on all tracks listed.
>
> Any Idea on how could I optimize this?
>
> Thanx in advance,
>
> Giulio
>
> Cantoberon Multimedia srl
> http://www.cantoberon.it
> Tel. 06 39737052
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
