Searching across multiple tables (best practices)

Question

I have property management application consisting of tables:

tenants
landlords
units
properties
vendors-contacts

Basically I want one search field to search them all rather than having to select which category I am searching. Would this be an acceptable solution (technology wise?)

Will searching across 5 tables be OK in the long run and not bog down the server? What's the best way of accomplishing this?

Using PostgreSQL

Serialize your tables and use [Xapian](http://xapian.org/) and [python-xapian](http://xapian.org/docs/bindings/python/) binding (wrapper). — tefozi, Jun 30 '09 at 05:30

casperOne · Answer 1 · 2012-12-03T20:39:40.483

7

Why not create a view which is a union of the tables which aggregates the columns you want to search on into one, and then search on that aggregated column?

You could do something like this:

select 'tenants:' + ltrim(str(t.Id)), <shared fields> from Tenants as t union
select 'landlords:' + ltrim(str(l.Id)), <shared fields> from Tenants as l union
...

This requires some logic to be embedded from the client querying; it has to know how to fabricate the key that it's looking for in order to search on a single field.

That said, it's probably better if you just have a separate column which contains a "type" value (e.g. landlord, tenant) and then filter on both the type and the ID, as it will be computationally less expensive (and can be optimized better).

edited Dec 03 '12 at 20:39

answered Jun 29 '09 at 16:42

casperOne

73,706
19
184
253

2

Upvoting to compensate the unexplained and peculiar downvote -- this answer, while concise, presents a perfectly reasonable idea (of course that view will also have primary keys for the actual tables and those keys should be kept distinct so that identifying what tables the various search hits came from is easy). – Alex Martelli Jun 29 '09 at 17:03
1

you can add a constant and distinct value into the sub-queries of the union to avoid needing unique PKs. – David Schmitt Jun 29 '09 at 17:46

David Schmitt · Answer 2 · 2009-06-29T17:47:39.910

4

You want to use the built-in full text search or a separate product like Lucene. This is optimised for unstructured searches over heterogeneous data.

Also, don't forget that normal indices cannot be used for something LIKE '%...%'. Using a full text search engine will also be able to do efficient substring searches.

edited Jun 29 '09 at 17:47

answered Jun 29 '09 at 16:43

David Schmitt

58,259
26
121
165

score 3 · Accepted Answer · answered Jun 29 '09 at 17:31

I would suggest using a specialized full-text indexing tool like Lucene for this. It will probably be easier to get up and running, and the result is faster and more featureful too. Postgres full text indexes will be useful if you also need structured search capability on top of this or transactionality of your search index is important.

If you do want to implement this in the database, something like the following scheme might work, assuming you use surrogate keys:

for each searchable table create a view that has the primary key column of that table, the name of the table and a concatenation of all the searchable fields in that table.
create a functional GIN or GiST index on the underlying over the to_tsvector() of the exact same concatenation.
create a UNION ALL over all the views to create the searchable view.

After that you can do the searches like this:

SELECT id, table_name, ts_rank_cd(body, query) AS rank
    FROM search_view, to_tsquery('search&words') query
    WHERE query @@ body
    ORDER BY rank DESC
    LIMIT 10;

score 1 · Answer 4 · answered Jun 29 '09 at 16:42

1

You should be fine, and there's really no other good (easy) way to do this. Just make sure the fields you are searching on are properly indexed though.

answered Jun 29 '09 at 16:42

Eric Petroelje

59,820
9
127
177

Searching across multiple tables (best practices)

4 Answers4

Linked