On 10/19/06, Jens Schipkowski <jens.schipkowski@xxxxxxxxxx> wrote:
On Thu, 19 Oct 2006 19:32:22 +0200, Merlin Moncure > > 1. your database design is the real culprit here. If you want things > to run really quickly, solve the problem there by normalizing your > schema. denomalization is the root cause of many, many, problems > posted here on this list. Believe it is normalized. We also seperated configuration and runtime data. And this is a runtime table. This table holds short living data for devices to be registered by a registration server. The INSERTs are triggered by external devices. The master data tables are perfectly normalized too. What you are seeing is not the real column names. I changed it due to readability. attr* have really different names and meanings. A "twin" (in real, initiator/member of the same conferencing group) is defined by these attributes. Due to high flexibility of this system (serverside configuration/ deviceside configuration for runtime) there is no other way to normalize.
ok, fair enough =) still, it feels odd that you are relating two tables on all 6 attributes. istm there is something more elegant possible, hard to say.
> 2. barring that, the above query will run fastest by creating > multi-column indexes on regtwin (attr*) fields. and reg(attr*). the > real solution to problems like this is often proper idnexing, > especially multi column. saying indexes take to long to build is like > saying: 'i have a problem, so i am going to replace it with a much > worse problem'. I will index it. Just prepared the test and will run it tomorrow. > 3. try where exists/not exists instead of where in/not in Did try it, before I switched to NOT IN. It was 10 times slower.
double check that when properly indexed. merlin
