🔒 Locked

[web] Help with SQL Query (optimization)

Started by ArchG Jun 14, 2006 at 9:52 AM 1 replies 400+ views

Original Post

Jun 14, 2006 at 9:52 AM

Basically I'm trying to grab 2 fields from one table and 1 field from another, we will call them Table1.Field1, Table1.Field2, and Table2.Field1..(so i can explain it a bit better) and I'm supplied with the Primary key of Table2, and the Foreign Key of Table1 (same value) here is what I have now


SELECT Table1.Field1, Table1.Field2, Table2.Field1 FROM Table2 INNER JOIN Table1 
ON Table2.PrimaryKey = Table1.ForeignKey WHERE Table2.PrimaryKey = " + myVariable

Is that how the query should be done? and is that as fast I can make it? I'm asking because I have a page that is taking a while to load sometimes, I don't know if it's this query or another one...I ask about this one because I'm not sure how well INNER JOIN works..Should I be using that at all? Thanks ArchG

Sander

Jun 14, 2006 at 4:40 PM

What SQL Are you using? MySQL? Try:

SELECT t1.field1, t1.filed2, t2.field3 FROM t1, t2 WHERE t1.key = t2.foreignkey AND t2.key = $var

markr

Jun 14, 2006 at 4:56 PM

It should make no difference whether you use the INNER JOIN keyword, or simply place all criteria in the WHERE clause.

If "The page takes a while to load sometimes", then you should benchmark the application in more detail.

Obviously any profiling should take place on an otherwise idle development server, NOT running the web browser locally (web browser + server fight for CPU time).

If you can reproduce the problem with a specific case on your otherwise idle dev server, then you can do some profiling to determine exactly what the cause is.

The most obvious culprit would be the fact that the appropriate indexes haven't been put on the columns you're joining.

Anything which is a primary key (the WHOLE key) will have an index placed on it automatically. The same is not true for foreign keys and each foreign key being used SHOULD usually have an index.

Try the benchmark with & without the index (selects only, make sure you have at least 100k rows in the table you're joining and the join matches relatively few of them (e.g. 100)) - you'll be amazed at the difference.

Of course one thing that a lot of people don't realise, is that indexes actually make INSERTs and UPDATEs slower.

It is possible to have too many indexes but difficult, so better to have lots than few.

Mark

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.