Is this query fine, and optimized

jayant

I have two tables:-

Table1

id <- integer, primary key of this table
served <- integer

Table2

id <- integer, primary key of this table
table1_id <- for relating the two tables
recieved <- integer

I want to select all rows of table2 such that:-
the corresponding row in table1 has "served" field greater than the "recieved" field.

For the same will the query:-

SELECT t2.* FROM table1 AS t1, table2 AS t2 WHERE t2.table1_id=t1.id AND t2.recieved < t1.served;

I don't have anyway to test this (PC down).

Please can someone test if this query will work the way it is expected to be.

Also is there a better more optimized way of achiving this?

pete_bisby

As a preference I normally use INNER JOIN within MySQL, so:

SELECT t2.* FROM table1 AS t1 INNER JOIN table2 AS t2 ON t2.table1_id=t1.id WHERE t2.recieved < t1.served;

If you are using a database that allows the use of sub-queries (e.g. Oracle), you could use the following query:

SELECT * FROM table2 AS t2 WHERE EXISTS (
SELECT id FROM table1 t1 WHERE t2.table1_id=t1.id AND t2.received<t1.served);

The difference here is that the MySQL query would go through all records within table1 and match against records in table2.
The Oracle query would only go through table1 until the criteria is met, then continue with the next record in table2, and so on ...

The Oracle query is quicker, but impossible with MySQL because of the sub-query.

jayant

Thanks pete_bisby.

My requirements have changed now.

Table1

id <- integer, primary key of this table
served <- integer
recieved <- integer
... other fields

Table2

id <- integer, primary key of this table
table1_id <- for relating the two tables
... other fields

out of

SELECT t2.* FROM table1 AS t1, table2 AS t2 WHERE t2.table1_id=t1.id AND t1.recieved < t1.served;

and

SELECT t2.* FROM table1 AS t1 INNER JOIN table2 AS t2 ON t2.table1_id=t1.id WHERE t1.recieved < t1.served;

which will work better on a large MySQL database (1000-5000 records in table1, 40,000-80,000 in table2)

and we cant use oracle

any other options?

pete_bisby

I had a go at re-creating your tables, with random values for the served and received. 4000 records were placed in table 1, and 60000 records placed in table2.

I ran both queries, and they both came back with the same time (0.46 seconds, resulting in 29882 matching records). The machine itself has an AMD Athlon 1100 processor, 512Mb memory, running Linux Redhat 8.0/MySQL 3.23.54

One thing I did, though, was place an index on the table1_id field. Seen as this is the relationship to table1, I thought it would be best.

As a preference, though, I always use the INNER JOIN query - and that's only because I've always used it in any SQL.

Hope this helps!

jayant

Oh thanks pete it was so wonderful of you.
I also did almost the same testing and got very little difference on my machine between the two querries

with innerjoin query being 0.0001 seconds faster 🙂
hence effectively almost the same.

tested on p3 700, 512mb ram, windows 98, with hdd @7200rpm