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:
- 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.
- 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?