1

Topic: Speed SELECT with an index and without

SELECT * FROM svod WHERE tin in (SELECT tin FROM svod WHERE pin = ' 123456789 ')

svod is a 5,3 million records in total amount 930 MB
There are indexes, including on TIN and PIN
To wait result it is impossible!
And if to throw out the index file - 0,5
Why? What mechanism? Simply interesting...
Thanks for involvement!

2

Re: Speed SELECT with an index and without

Redrik;
1. Indexes do not correspond to request;
2. Presence of the active index.

3

Re: Speed SELECT with an index and without

The correct transiting;
Thanks for a response in output evening! smile
1) indexes the elementary, fields such:
PIN - A C (10)
TIN - N (10,0)
2) the active index? - In no event! Is a business known...

4

Re: Speed SELECT with an index and without

Redrik wrote:

the correct transiting;
Thanks for a response in output evening! smile
1) indexes the elementary, fields such:
PIN - A C (10)
TIN - N (10,0)

Meanwhile fields, instead of expressions of indexes here are described. Index expressions JUST THE SAME? Precisely? Reindex for a long time did?
ps: How many time spoke to me, punching itself a heel in a breast that "indexes are, necessary", but then, on closer examination all appeared absolutely in another way. Including with understanding knocking on a breast of concepts "index", "the index expression","a necessary index!.

5

Re: Speed SELECT with an index and without

What for so? Can at once

SELECT * FROM svod WHERE pin = ' 123456789'

Subqueries a piece turbid. In  I do not use them. In MSSQL happens, but also to it a roof breaks sometimes.
Generally I do so

SELECT tin FROM svod WHERE pin = ' 123456789 ' into cursor ttin
index on tin tag tin
SELECT * FROM svod WHERE indexseek (tin.F., ' ttin ', ' tin ')

6

Re: Speed SELECT with an index and without

Redrik wrote:

Why? What mechanism? Simply interesting...

SYS (3054) look that produces

7

Re: Speed SELECT with an index and without

the transiting. wrote:

... Punching itself a heel in a breast...

Simply I do not want to load superfluous letters, and not the student for a long time smile
Pin - A C (10)-> INDEX ON Pin TAG Pin
Tin - N (10,0)-> INDEX ON Tin TAG Tin
REINDEX precisely it is not necessary - indexes are made only and that from zero (I will specify - CDX was not generally)!

8

Re: Speed SELECT with an index and without

Dima T wrote:

What for so? Can at once

SELECT * FROM svod WHERE pin = ' 123456789'

Not it!
Conditionally: Pin - the code of the person, Tin - the firm code
All people on all firms where the specific person has been noted are necessary to me... wink
Subqueries too I do not love, but somehow so it was added and dumbfounded a little!
SYS (3054) - I will look - I will unsubscribe
Thanks!

9

Re: Speed SELECT with an index and without

Gloom!
Cut down basis from 5 million records to 0.5 million - did not wait!
Left casual 100 KB...

SELECT * FROM svod WHERE tin in (SELECT tin FROM svod WHERE pin = ' 123456789 ')

Rushmore optimization level for table svod: none
Using index tag Pin to rushmore optimize table svod
Rushmore optimization level for table svod: full
Joining table svod and table svod using index tag Tin
Somehow so...

10

Re: Speed SELECT with an index and without

SET COLLATE TO "MACHINE" and  an index;
! To delete and create anew.

11

Re: Speed SELECT with an index and without

bmvmon wrote:

SET COLLATE TO "MACHINE" and  an index;
! To delete and create anew.

Yes-yes, mandatory. On PIN, for it character. Singularities last SP and fixes VFP9.

12

Re: Speed SELECT with an index and without

COLLATE it is invariable = "MACHINE" sad

13

Re: Speed SELECT with an index and without

Redrik wrote:

COLLATE business in not/identity collate is invariable = "MACHINE" sad

Here at index creation and at usage. At different indexes on character fields do not work. For this reason the temporary index  works, and constant - is not present. That the index  worked it is necessary to CREATE at that value collate at which its usage is expected. And once again - speech about indexes on CHARACTER fields.

14

Re: Speed SELECT with an index and without

here a problem in index misuse, i.e. the request execution plan is incorrectly selected.
This join the suspicious:

Redrik wrote:

the Gloom!
Rushmore optimization level for table svod: none
Using index tag Pin to rushmore optimize table svod
Rushmore optimization level for table svod: full
Joining table svod and table svod using index tag Tin

15

Re: Speed SELECT with an index and without

wrote:

In itself FoxPro cannot give such difference in runtime

Can. FoxPro not gods wrote.
Itself came across on similar on the table in 10 + million records. At creation of the execution plan of request by the optimizer that index and the error price is selected not it turns out is too high because of the big size of the table.
PS With MSSQL such too happens.

16

Re: Speed SELECT with an index and without

wrote:

That it can be

Thanks, Vladimir that paid attention to my question!
Itself SELECT in such type, probably, too "unreasonable" it turned out...
Idle time WHERE a.tin=b.tin instead of IN () works almost instantly!
Under your remark:
- The table and indexes are created from zero - damages cannot be
- Other indexes, except PIN and TIN, no
- Value PIN in different tests same (sampling really turns out big - about 40 % of all basis)
- The table and index - local, the machine is physically disconnected from a network ( not for a grid)
- Places on a disk nearby 120  (SSD)
- The antivirus is not present (the machine not in a network, flash cards anyhow are not put, access is restricted)
I hope this topic at least helps somebody not to get from readers on a rake...
Once again thanks for involvement in arguing!