Mysql Inner Join Query Mysql vs Access

[Herby1976]

I run a Mysql server 5.1 as test on a little server. I compare the same SQL statement whit Mysql and access. Whene i use de sql statement "select dconlinelog.logtabel.* , klanten.klanten.* from(dconlinelog.logtabel)inner join klanten.klanten ON dconlinelog.logtabel.Log_KLNR = klanten.klanten.Klantnummer"

I run it on acces with oledb connection and Mysql with the connector from Mysql. Access do this sql at 10 sec and mysql need 55 sec to do the same. I found out that the cpu is the bottleneck for the query. The application runs on the server so the same cpu i use for access. From where the big difference?? (With simpel sql statement is mysql 10 times faster then access)

 

[r937]

did you declare indexes on the mysql tables?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Herby1976]

klanten.klanten.Klantnummer is the primairy key for that table so also automatic a index for that table.

 

[DonQuichote]

So what you basically want is an index on logtabel.Log_KLNR as well. If they have the same data type, you can even define a foreign key relation (like in access).

If you want to see how the query is optimized, put an EXPLAIN command in front of it (only works with select as far as I know):

EXPLAIN select dconlinelog.logtabel.* , klanten.klanten.* from(dconlinelog.logtabel)inner join klanten.klanten ON dconlinelog.logtabel.Log_KLNR = klanten.klanten.Klantnummer

The result will tell you which indexes are used, if any.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[DonQuichote]

Something else: you did make it a pass-through query, don't you? Otherwise, MS-Access parses the query itself (using its own SQL dialect and you can use MS-Access functions) and has to "download" the entire tables to be able to. Even if you request just one record.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[Herby1976]

Ok gone try to define a foreign key relation. First try give some errors but gone try it again later again.

 

[Herby1976]

The relations solve the problem. Do it now in less then 0.5 seconds. Thnaks for the help