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
