optimizing queries

could creating views greatly optimize the efficiency of a set a queries?

for example, imagine that i have a table with thousands of records that i am self joining. if i did something like this:

SELECT t1.id , t2.id
FROM table t1, table t2
WHERE t1.s= t2.e AND t1.id = $id1 AND t2.id = $id2;

it would take a long time to cross multiply the table.

but what if i did this (forgive the syntax i have never created a view before):

CREATE VIEW t1 (*)
AS SELECT * FROM table WHERE id = $id1;

CREATE VIEW t2 (*)
AS SELECT * FROM table WHERE id = $id2;

SELECT t1.id , t2.id
FROM table t1, table t2
WHERE t1.s= t2.e;


in my case creating the views would eliminate many rows, leaving on average around 20. so joining those two together should a bit better.

am i correct?

i guess the better question would be, does the database system join the tables or eliminate rows first

Views are commonly used to simply complex sql statements and/ or to hide underlying data structure from end users as well as for a number of other reasons. A view will still query the underlying tables and their is still the same cost as if you performed the query on the original table. Depending on your database system you could create heap tables or derived tables that are updated independently of the users query.
...
For optimization you will want to ensure that your tables are indexed, that queries are written to take advantage of existing indexes, that your data types are small as possible that your tables are analyzed and optimized when needed.

so you are saying that a join statement on a view (that has about 5% the number of rows as the original table) will take as long as a join on the original table?

do you know anythingabout temporary tables?

If you are using Oracle you can use something called a materialized view. It keeps the data pre-summarized, but a normal view does not increase performance. As said above it just hides complexity.

Your sql is just merged with the sql of the view.

gotcha.

unfortunately this is a homegrown project, and i will be using mysql

do you know anything abuot temporary tables?

If you are using mysql you could look into using heap tables.