Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have created two tables

create table one(ref_no int, name varchar(100), design varchar(100));

create table two(SNo int auto_increment primary key, use_date Date, ref_no int references one(ref_no), start_time DateTime, end_time DateTime, tot_time int);

They are as follows:

Table "one"

+--------+--------+------------+
| ref_no | name   | design     |
+--------+--------+------------+
|      1 | tushar | new_design |
+--------+--------+------------+

Table "two"

+-----+------------+--------+---------------------+---------------------+----------+
| SNo | use_date   | ref_no | start_time          | end_time            | tot_time |
+-----+------------+--------+---------------------+---------------------+----------+
|   1 | 2013-01-15 |      1 | 2014-00-00 00:00:00 | 2016-00-00 00:00:00 |     7200 |
+-----+------------+--------+---------------------+---------------------+----------+

There will be entries in table "two" which are sorted by date. Queries will be for tot_time of a user or a design in a given range of date.

For the given range of date, ref_no will be selected from table "two" and then a join applied with table "one" to get only those ref_no where name is the same as required in the query.

There are 2 approaches I have thought of:

  1. select entries in range date from table "two". select the fields SNo and ref_no. Then apply the JOIN and from table two, add tot_time of SNo that are selected after applying after the join.
  2. select entries in range date from table "two". select the fields ref_no, start_time, end_time and tot_time. Then apply the join and add tot_time for entries which are selected after the join.

Selecting start_time and end_time is required as end_time might be NULL for some cases, so they have to be dealt in a different manner. SO I cannot just select ref_no and tot_time.

Which of these would be faster? Getting the SNo and then accessing table "two" again after the JOIN OR creating a temporary table with 4 fields and then joining it with another table?

share|improve this question

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.