SELECT *
The FROM dbo. MmImportOrderDtl iod
The JOIN dbo. MmImportOrderHdr ioh ON iod. UmmImportOrderHdrId=ioh. UmmImportOrderHdrId AND iod. BUse=1 AND ioh. BUse=1
The JOIN dbo. MesColour c ON c.u ColourId=iod. UColourId
Where ioh. SOrderNo='422576-5656

Use above statement query speed, less than 1 s

The SELECT c.s. ColourCNName
The FROM dbo. MmImportOrderDtl iod
The JOIN dbo. MmImportOrderHdr ioh ON iod. UmmImportOrderHdrId=ioh. UmmImportOrderHdrId AND iod. BUse=1 AND ioh. BUse=1
The JOIN dbo. MesColour c ON c.u ColourId=iod. UColourId
Where ioh. SOrderNo='422576-5656

The same just the output field is reduced to a, query speed is become 34 s

Strives for the great god, never met this kind of situation, the data in the table only primary key index, other have nothing to do

CodePudding user response:

SColourCNName fields indexed

CodePudding user response:

With the index, the effect is still the same

CodePudding user response:

Just do the other test, found that as long as it is with the dbo. MesColour this form related queries are slow, are associated with this table primary key ID query, this is what problem, before always is normal, just happened in recent days, the middle operation what also have no to this table do

CodePudding user response:

If is the dbo mesColour this insert temporary table, then through a temporary table to link, data query speed is normal, solving

CodePudding user response:

The best way is to put the execution plan, play out and have a look

CodePudding user response:

You optimize table mesColour try this command, you get the temporary table quickly, and associate the table slowly, estimation is table too fragmented

CodePudding user response:

Suggest to add index and have a look

CodePudding user response:

Mysql can query across server table more correlation?

192.168.0.1 server users table 10 million data
192.168.0.2 server user table there are 10 million data

I want to join the two tables through the SQL statement, this is not equivalent to the distributed database?

Is there such a SQL statement?

CodePudding user response:

If you this field and other fields have joint index,