Da: [EMAIL PROTECTED]
Oggetto: Re: can you help me optimizing this query?
Data: 12 luglio 2004 19:10:36 CET
A: [EMAIL PROTECTED]Hi Shawn, this is the last version of my intermediate table.
with this version the query is absolute speedy. I have added an index to both the columns I use for the ON clause of the left join, and I'm absolutely satisyed with the performance now.
is this the correct way to optimize performance on with left join?
CREATE TABLE `brani_caratteri` ( `bc_id` int(11) NOT NULL auto_increment, `bc_id_brano` int(11) NOT NULL default '0', `bc_id_carattere` int(11) NOT NULL default '0', KEY `id_genere` (`bc_id`), KEY `bc_id_brano` (`bc_id_brano`), KEY `bc_id_carattere` (`bc_id_carattere`) ) TYPE=MyISAM
legend:
brani means track; carattere means character;
Il giorno 12/lug/04, alle 18:59, [EMAIL PROTECTED] ha scritto:
Do you have all of the right indexes on your tables to assist MySQL in creating your JOIN? Show us the results of SHOW CREATE TABLE for your 3 tables, please...
Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
|---------+----------------------------> | | Giulio | | | <[EMAIL PROTECTED]| | | n.it> | | | | | | 07/12/2004 08:50 | | | AM | | | | |---------+---------------------------->---------------------------------------------------------------------- ----------------------------------------------------------|| |
| To: MySQL List <[EMAIL PROTECTED]> |
| cc: |
| Fax to: |
| 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]
Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052
