4 messages in com.mysql.lists.javaRE: SQL Performance on versioning
FromSent OnAttachments
Jim Tyrrell12 Jan 2006 12:37 
Frondoni, Giorgio12 Jan 2006 12:51 
Jim Tyrrell12 Jan 2006 12:59 
Fabian Schmidt12 Jan 2006 13:43 
Subject:RE: SQL Performance on versioning
From:Jim Tyrrell (jimt@yahoo.com)
Date:01/12/2006 12:59:30 PM
List:com.mysql.lists.java

Giorgio,

Yeah sorry I should have mentioned that...that works great..crazy fast..but what if I also want the FeatureGroupID things of 1, 2, and 3 etc. aka

FeatureID, FeatureGroupID, FeatureName 1, 1, "Version 1" 2, 1, "Version 2" 3, 1, "Version 3" 4, 1, "Version 4" ... 999, 1, "Version 999"

1000, 2, "Version 2 1" 1001, 2, "Version 2 2" 1002, 2, "Version 2 2"

Your query only gives me "Version 2 2" and I also want Version 999.

Thank you so very much for that, but hopefully the answer is out there.

In other words I only then will get the highest record in that set. A long time ago on this project I did some queries like that..but the only way I could get them to work was with something like the following:

Select FeatureName from Feature F where FeatureID = (Select FeatureID from Feature where FeatureGroupID = F.FeatureGroupID)

Thank You Jim Tyrrell

--- "Frondoni, Giorgio" <gior@TransCore.com> wrote:

Jim,

Have you try to write the statement as indicated below?

Select FeatureName from Feature where FeatureID = (Select max(FeatureID) from Feature)

Regards,

Giorgio Frondoni AVP, Chief of System Development TransCore phone: (858) 826-4750 cell: (760) 214-4092

-----Original Message----- From: Jim Tyrrell [mailto:jimt@yahoo.com] Sent: Thursday, January 12, 2006 12:38 PM To: ja@lists.mysql.com Subject: SQL Performance on versioning

Everyone,

I finally feel let down by mysql after 5 years of great use. I am sure I am being a dummy on this, but am wondering if there is some setting somewhere to help out a query like this.

Give a table like this: FeatureID is an autonumber and the group field keeps things together aka version control. FeatureID, FeatureGroupID, FeatureName 1, 1, "Version 1" 2, 1, "Version 2" 3, 1, "Version 3" 4, 1, "Version 4" ... 999, 1, "Version 999"

Over small sets I write a query like this: Select FeatureName from Feature F where FeatureID = (Select max(FeatureID) from Feature where FeatureGroupID = F.FeatureGroupID) Giving me Verison 999

In MySQL for 1000 versioned records this takes almost half a second on my machine. On my machine for MS SQL this returns right away basically in no time. I make sure cache is not in play by inserting a set of records and then running the query. Also the FeatureGroupID is indexed and so is the featureID.

Is there a setting to make this work in mysql administration somewhere? Is there a word or set of words that I can search for? I have looked high and low and have not been able to make this work fast enough for me today.

Someone I work with suggested using group by and this seems to be even worse then what I am doing in the above query. hmmmm!!!!

Someone please help.

karma is yours if you can help me on this.

Thank You Jim Tyrrell

-- MySQL Java Mailing List For list archives: http://lists.mysql.com/java To unsubscribe:

http://lists.mysql.com/java?unsub=gior@transcore.com