Better way to indexing...

radek225

Registered User.
Local time
Today, 02:38
Joined
Apr 4, 2013
Messages
307
I have a form with 9 text fields and list. If you fill field, on the list, are shown data filtered by this criteria. If you fill another filed, then previous criteria are reset and then we will see data on the list with new criteria.
Now I try to indexing tables as good as it could be. So I have a question, which variant I should choose (please see attached files)? - Data in the circle are indexing. If You want I can write query form my form.
 

Attachments

  • 1.png
    1.png
    36.4 KB · Views: 135
  • 2.png
    2.png
    33.4 KB · Views: 118
I dont speak your language, but your joins seem considerably off... It is rare to have arrows running to one table, much more common it is to run the arrows from one table to many tables.

What is exactly your question around indexing though, I dont understand it ?
 
1) Hmm. e.g. tblOrders and tblTypeOfOrders. When arrows run from tblTypeOfOrders to tblOrders, this means that "Take all records from tblTypeOfOrders and only these records from tblOrders where fields are equal". So I think it isn't wrong. But when I run the arrows in the other side I don't see any difference when I view the data. In which cases the direction of the arrow is important?
2) Which of fields I have to indexing in order to achieve the fastest search in the database via a form? The biggest problem I have with relations, which one of side relation, should be indexing (please see difference between 1 and 2 attachment)?
 
1)
Well it could be you shouldnt need an arrow at all, however if you need it... it is crucial. If you dont need it is a performance hit that you dont need, outer joins perform a bit less well than inner joins.
Lets try and dream up an example, lets say you have a customer without orders but you want to see all your customers.
Customer ----> Orders
The arrows you only need when you want to see anything always and allow "blank" fields. Usually you have a central table like Customers that you will always want too see... An order without a customer is kind off nonsense.

In your situation, to have a typeoforder without having an actual order is kind off nonsense.
The question then is, is it possible to have an order without knowing what typeoforder it is?
If the answer is Yes then you want tblOrders ----> tblTypeOfOrders
If the answer is No then you want tblOrders ------ tblTypeOfOrders
The arrows even if they "dont seem to do anything" do mean something and have (huge) impact on your database design.

2) for the best performance you ALWAYS want KEY values to be indexed, like your id_status and id_k. It is also quite common to see foreign keys indexed 100%, though this depends a bit on you database design.
If for example you want to search quite often on your status (inside your tblStatus) and find the tblZlecenia (orders?) that go with that status then you definately want that FK to be indexed.
If your FK is simply a reference field that will never be searched then you can concider skipping the Index.
Simularly any field (of a significant size table, lets say 10.000 records or more) that will be searched a lot will want/need an index.

If you stick to those kind of rules, you should be good enough index and performance wize.
Without knowing very much the details of your database and requirements, I cant tell you which columns to index and which not to index....
 

Users who are viewing this thread

Back
Top Bottom