Why is query so slow?

dave5398

I have 3 tables, let's call them A, B and C. Each has an auto_increment ID int field which is the primary index on each. I have many-many relationships between A and B and between B and C using junction tables AB and BC which simply consist of the ID's of the corresponding tables A,B,C where a relation exists. I want a query containing all relationships so I tried:

SELECT * FROM A INNER JOIN AB ON A.ID=AB.A_ID INNER JOIN B ON AB.B_ID=B.ID INNER JOIN BC ON B.ID=BC.B_ID INNER JOIN C ON BC.C_ID=C.ID ORDER BY A.ID,B.ID,C.ID

Now each table only contains a few items (around 20 each) but it seems to run incredibly slowly and last time it timed out as taking over 30 seconds! The similar query in MS Access (which I modified to SQL) runs instantly which I would have expected.

Anyone got any ideas what's going wrong?

Dave

cgraz

Do you have indexes on all of those id fields?

Do you really need all fields? SELECT * as opposed to SELECT field1, field2...?

You have no WHERE clause. Could you utilize a WHERE clause?

NogDog

(Caveat: I am not a database "guru", so take the following with a grain of salt.)

Since the intersection of the AB and BC tables would seem to be the driving factor behind the row selections, I would do that first, then get the matching rows from the A, B, and C tables:

SELECT A.col1, A.col2, B.col1, B.col2, C.col1, . . . C.colN
   FROM AB
      INNER JOIN BC ON AB.B_ID = BC.B_ID
         INNER JOIN A ON A.ID = AB.A_ID
            INNER JOIN B ON B.ID = AB.B_ID
               INNER JOIN C ON C.ID = BC.C_ID
   ORDER BY A.ID,B.ID,C.ID

Assuming that the ID columns of A, B, and C are the primary keys (and thus already indexed) you'd want to make sure that the A_ID, B_ID, and C_ID columns in the two relation tables are all indexed.

dave5398

Thanks for the responses.

I indexed all fields which relate any two tables. I am not sure, but I don't think the order of the inner joins would help. No WHERE clause is needed because the junction tables pick out the records I need.

After doing a "die" before the main loop, it seemed to go OK (but there does still seem a small delay) so it seems the problem is more in the translation from VBA to PHP.

Final question: does SELECT rather than SELECT field1, field2... make an appreciable difference to execution speed? I would assume that the query result is just a complex set of arrays of record locations and that actual fields are only retrieved during a fetch operation in which case SELECT should go just as fast as SELECT field1, etc. Can anyone confirm this?

Dave

NogDog

I think the main impact of "*" versus a field list is simply the amount of data that that must be transferred between the DBMS and the requesting program. I doubt that it has too large of an impact on the query execution speed within the DBMS itself, but then the results have to be transmitted to (or pulled by?) the application, and if you only actually need a certain sub-set of all those columns, it would seem to be a waste of resources to be transmitting every column of every table in the query for each result row.

Also, specifying a field list helps with the readability of the code for future maintenance (making it obvious which fields will be used), plus it ensures that the result rows will always be organized in a specific manner even if the tables should be modified in the future in a manner that would otherwise rearrange the result column sequence if "*" were used.

Jramz

dave5398 wrote:
Thanks for the responses.

I indexed all fields which relate any two tables. I am not sure, but I don't think the order of the inner joins would help. No WHERE clause is needed because the junction tables pick out the records I need.

After doing a "die" before the main loop, it seemed to go OK (but there does still seem a small delay) so it seems the problem is more in the translation from VBA to PHP.

Final question: does SELECT rather than SELECT field1, field2... make an appreciable difference to execution speed? I would assume that the query result is just a complex set of arrays of record locations and that actual fields are only retrieved during a fetch operation in which case SELECT should go just as fast as SELECT field1, etc. Can anyone confirm this?

Dave

I use a fairly extensive MySQL DB for my friends CD inventory database:

http://www.soonerslivehere.com/CDDQ/ShowArtists.php

and i tried timing the differences between doing a pull all artists with a field list and just using the "*" and i can say with some certainty that there is little to no appreciable difference between the two.

Hope that helps!!!

~Jeremy

Piranha

Jramz, on a small database like that one, and with only a small load, it would not make a big difference. But when there are millions of rows in the tables, and when there are maybe 100 queries/second it may make a big difference. I haven't handled such big and busy databases, so I don't have empirical data, but to save a couple of milliseconds on each query would mean pretty much.

But in your application you have a big security flaw. To test it I tried to check out the first album (a*teens) as Jeremy, and it worked. I won't try to delete or something like that since I don't want to ruin things. But to make it possible for anyone to make changes may cause you big trouble. I recommend that you first remove the link and then at least demand a password to let people change things.

Jramz

Piranha wrote:
Jramz, on a small database like that one, and with only a small load, it would not make a big difference. But when there are millions of rows in the tables, and when there are maybe 100 queries/second it may make a big difference. I haven't handled such big and busy databases, so I don't have empirical data, but to save a couple of milliseconds on each query would mean pretty much.

But in your application you have a big security flaw. To test it I tried to check out the first album (a*teens) as Jeremy, and it worked. I won't try to delete or something like that since I don't want to ruin things. But to make it possible for anyone to make changes may cause you big trouble. I recommend that you first remove the link and then at least demand a password to let people change things.

That link is just a demo/testing server... the real data that we use now is securely stored on our INTRANet.... i appricate your advice though! 😉