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/ \_/
Attachment:
signature.asc
Description: This is a digitally signed message part.