FAQ Search Today's Posts Mark Forums Read
» Video Reviews

» Linux Archive

Linux-archive is a website aiming to archive linux email lists and to make them easily accessible for linux users/developers.


» Sponsor

» Partners

» Sponsor

Go Back   Linux Archive > Debian > Debian User

 
 
LinkBack Thread Tools
 
Old 12-18-2010, 09:25 PM
Bernard
 
Default Mysql database and OO_base.org: problem with INNER JOINTS on tables

Hi to Everyone,

I am trying to manage a local MySQL database which is connected to
OpenOffice.org_base using JDBC. The main table has about 90 fields and
25,000 elements. This table comprises a dozen of fields about 'places'
(birthplace, weddingplace, deathplace, burialplace, workplace... ),
which fields are not filled in plain text, but in postal codes. I have
another table that displays placenames vs postal codes.


I did not have much problem figuring how to get an OO_base request to
display the main table with placenames in clear, AS LONG AS IT WAS
MATTER OF DISPLAYING ONLY ONE place field in clear, together with all
other fields (fields others than 'place' fields as well as coded
placefields).


Problems started as soon as I tried displaying two place-fields in
clear. To do this, I created two JOINTURES between fields in two tables
(was only one jointure when the matter was to display only one place field).


While the request succeeded after about one minute in the first case, it
never ended in the second test ; I had to get out after half an hour using


#kilall -KILL mysqld

I then tried with a much shorter table, that is, the same table with
only 1000 elements, the remaining 24,000 having been erased. Then, it
did work... after about 10 minutes, which is a lot of time for such a
small table... Yet I did not get exactly what was expected :

only the lines where both placefields were NOT NULL did display.
Whatever line with only one NOT NULL placefield, did not appear at all,
while I expected that they all show up, with an empty column where the
placefield was empty !


It is therefore clear that I did not use the right process !

Thanks in advance for your help


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Archive: 4D0D34CB.3020809@teaser.fr">http://lists.debian.org/4D0D34CB.3020809@teaser.fr
 
Old 12-19-2010, 08:20 AM
"Boyd Stephen Smith Jr."
 
Default Mysql database and OO_base.org: problem with INNER JOINTS on tables

In <4D0D34CB.3020809@teaser.fr>, Bernard wrote:
>I am trying to manage a local MySQL database which is connected to
>OpenOffice.org_base using JDBC. The main table has about 90 fields and
>25,000 elements. This table comprises a dozen of fields about 'places'
>(birthplace, weddingplace, deathplace, burialplace, workplace... ),
>which fields are not filled in plain text, but in postal codes. I have
>another table that displays placenames vs postal codes.
>
>I did not have much problem figuring how to get an OO_base request to
>display the main table with placenames in clear, AS LONG AS IT WAS
>MATTER OF DISPLAYING ONLY ONE place field in clear.
>
>Problems started as soon as I tried displaying two place-fields in
>clear.

>While the request succeeded after about one minute in the first case, it
>never ended in the second test ; I had to get out after half an hour.
>
>I then tried with a much shorter table, that is, the same table with
>only 1000 elements, the remaining 24,000 having been erased. Then, it
>did work... after about 10 minutes, which is a lot of time for such a
>small table...

Sounds like you need some indexes. They vastly speed up join operations,
usually. You'll want an index in the "postal_code->place_name" table on the
columns that are compared in the query. If your use equality (<> or =
operators) on some columns, and ordering (<, <=, >=, or > operators) on
others, list the ones using equality in the query first.

Without indexes, doing each join is usually done with 2 (or more) nested table
scans. This requires (#rows in main table * #rows in joined table)
"operations". Even if each individual operation is fast, that number can get
quite big quite fast.

With a good index, doing each join requires logarithmically fewer operations.
(E.g. instead of 1000 * 1000 operations, 1000 * 10 operations; instead of
25000 * 100000 operations, 25000 * 17 operations.)

>Yet I did not get exactly what was expected :
>only the lines where both placefields were NOT NULL did display.
>Whatever line with only one NOT NULL placefield, did not appear at all,
>while I expected that they all show up, with an empty column where the
>placefield was empty !

That's the default when doing a JOIN. It is called an "inner join". If you
want to do a left/right/full outer join, there is an ANSI SQL syntax and MySQL
might also provide an alternative syntax.

You should check the results of your query that displays only a single place-
field "in the clear".

All this requires learning some SQL and being about to design a query in SQL
that is used by base. I know little to nothing about the UI provided by base.
--
Boyd Stephen Smith Jr. ,= ,-_-. =.
bss@iguanasuicide.net ((_/)o o(\_))
ICQ: 514984 YM/AIM: DaTwinkDaddy `-'(. .)`-'
http://iguanasuicide.net/ \_/
 

Thread Tools




All times are GMT. The time now is 11:38 PM.

VBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2007, Crawlability, Inc.
Copyright 2007 - 2008, www.linux-archive.org