Search Postgresql Archives

Re: Please suggest me on my table design (indexes!)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



DaNieL wrote:
Hi guys, im tryin to optimize a simple table, suited for contain
users.
So, my table at the moment is:

-----
CREATE TABLE contacts(
 id BIGSERIAL PRIMARY KEY NOT NULL UNIQUE,
 company_id BIGINT,
 code varchar(10),
 company_name varchar(120),
 name varchar(120),
 surname varchar(120),
 phone varchar(80),
 email varchar(80),
 kind varchar(8)
);
-----

I use this layout in order to store 3 kind of users: Private, Company
and Company's Employee.. the col 'kind' infact will contain just
'private', 'company' or 'employee', but is unnecessary, i can
understand what kind a user is by those rules:
Private are stand-alone users, company_name and company_id are always
NULL;
Company have the company_name;
Employees have the company name and the company_id (with the id of the
company's row);

The layout looks find although i would not use Bigserial unless you expect to exceed 2.1 billion records

Example:

id|company_id|code| company_name|name|surname|phone|
email               |   kind
1 |NULL         |C001| Sunday Inc.     | John | Doe      |88888 |
j@xxxxxxxxxx | company
2 | 1              |E001| Sunday Inc.     |Paul  | Smith   | 77777|
smith@xxxxxxxxxx| employee
3 | NULL        |P001| NULL              | Rose | Mary   | 66666|
rose@xxxxxxxx |  private

So, first of all, does this layout looks good?
Before i used to keep employees in a different table, becose usually
employees have just few data (name, surname, direct email and direct
phone.. all the addresses, bank data, etc.. belongs tot he company),
but noe i preferred this way to avoid constant inner joins.

Now, i aspect that my users will search the contact table just for the
fields company_name, name, surname, email, code.
That kind of query cant be as
WHERE company_name = '$x'
but will be much like
WHERE company_name LIKE '$x%',
both becose i use an autocomplete field for the quick search, both
becose.. well, that's how users search data's (in my experience).

What i have done with searches on small strings where the user is unsure what they are looking for or the spelling, I do something like this

where substr(company_name,1,length($searchtext$ UserSearchString $searchtext$)) ilike $searchtext$UserSearchString $searchtext$

and mix it with soundex. This way the user get a list of possible matches with only handful to type characters

Draw back is this type of search is it can't be indexed.
So i created those index, to let the query planner use the indexes in
the LIKE query:

My doubt is: am i using too many indexes?
Will my insert/delete/update queryes be too slow, and does the select
optimization worth the price? (and, does this way really optimize the
selects queryes?)

The more indexes you have the slower updates will be. Yet not a horrible amount. The answer to this is it depends on the work load can the system suffer the overhead of the indexes and still give adequate results on queries.
Consider that my application wont do many insert-delete-update
sequentially.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux