[Resolved] php/mysql 4 vs. MS Access

[KoshNaranek]

Long no see :-)

I've got a strange problem, maybe someone can help me figure out, where the problem is...

in PHP (on a Linux-Webserver with a local mysql-server) this Query:

SELECT EVN_Nodes.type AS Dienst, Right( EVN_DTAG.datum, 7 ) AS Zeitraum, EVN_Nodes.memo AS Node, EVN_DTAG.zielrufnr AS Zielrufnummer, Sum( EVN_DTAG.nettogesamt ) AS Nettosumme
FROM EVN_Nodes
INNER JOIN EVN_DTAG ON EVN_Nodes.anschluss = EVN_DTAG.anschluss
GROUP BY Zeitraum, Node, Zielrufnummer
HAVING (
Dienst = "DATA" AND Zeitraum = "01.2004"
) LIMIT 0 , 30 

takes about 50 seconds.

phpMyAdmin -> about 50 seconds...

The same Query, built with Access-Wizard, querying via ODBC takes only 5 seconds.

This drives me crazy... I've set Indexes... renamed fields... No change. Access always wins the race.

Can anyone tell me, why? What's wrong with my Query?

When will MYSQL support "Views" like MS-SQL-Server??? :rolleyes:

[csn]

Views are currently being implemented, and will appear in the 5.0 or 5.1 version of MySQL Server. Unnamed views (derived tables, a subquery in the FROM clause of a SELECT) are already implemented in version 4.1.

[KoshNaranek]

Ok... one of two 😉

I'll have a look at the manual and check, if this may be helpfull...

The question "Why is Access so much faster?" is still not solved 🙁

BTW: Here is, what i get on the mysql-console:

mysql> SELECT EVN_Nodes.type AS Dienst, Right( EVN_DTAG.datum, 7 ) AS Zeitraum, EVN_Nodes.memo AS Node, EVN_DTAG.zielrufnr AS Zielrufnummer, Sum( EVN_DTAG.nettogesamt ) AS Nettosumme
-> FROM EVN_Nodes
-> INNER JOIN EVN_DTAG ON EVN_Nodes.anschluss = EVN_DTAG.anschluss
-> GROUP BY Zeitraum, Node, Zielrufnummer
-> HAVING (
-> Dienst = "DATA" AND Zeitraum = "01.2004"
-> ) LIMIT 0 , 30;
+--------+----------+-----------------------------+---------------+------------+
| Dienst | Zeitraum | Node | Zielrufnummer | Nettosumme |
+--------+----------+-----------------------------+---------------+------------+
Blablabla....
+--------+----------+-----------------------------+---------------+------------+
11 rows in set (44.49 sec)

i'am now experimenting with navicat.
Looks like the position of the "GROUP BY" does the trick...

SELECT 
       RIGHT(EVN_DTAG.datum, 7) AS Zeitraum,
       EVN_Nodes.memo,
       EVN_DTAG.zielrufnr,
       EVN_Nodes.type,
       SUM( EVN_DTAG.nettogesamt ) AS SUM_OF_nettogesamt
FROM EVN_Nodes
   INNER JOIN EVN_DTAG ON (EVN_Nodes.anschluss = EVN_DTAG.anschluss)
WHERE 
   (
      (EVN_Nodes.type = 'DATA' AND RIGHT(EVN_DTAG.datum,7)='03.2004')
   )
GROUP BY RIGHT(EVN_DTAG.datum, 7), EVN_Nodes.memo, EVN_DTAG.zielrufnr, EVN_Nodes.type
ORDER BY EVN_DTAG.zielrufnr

is much faster... (5 seconds)

Maybe i shoud not use Access, to create such querys...

:rolleyes: Where is my book "SQL in 21 Days...." Think, i'll need it 😃