I have a large table sample_data (say 4 million rows) there is a 1to1 relationship with sample_detail
I am ruining a query against sample_data and the query checks a value on the related table.
since most queries will need to reference the related data. Is it better to create a view to include the sample_data and the columns from sample_detail and run my quires against it instead of directly on the sample_data?
restated : if I have a large table with a 1to1 relationship with another table, is it “faster” to create a view with all columns of both tables or just use the table directly.
Thanks
H
Hi @hharrington
if I have a large table with a 1to1 relationship with another table, is it “faster” to create a view with all columns of both tables or just use the table directly?
No, view will not help you to speed up query since DB still will run full query with joins “under the hood”. That is how views in DB work - DB simply combines your query with that which was used to build a view. Due to this views can work even slower in some cases than a simple query.
In your case you should add indexes on columns in both tables which participate in where clause to speed up your query.
Regards, Andriy
Thank you for the response and I have one follow up question about indexing
its seems indexing a column is not set as default which would imply not every column should be indexed. can you point me to some doc that discusses this in detail? but the driving question in what situation would indexing be a bad idea and why
thanks,
H
Indexes are used to speed up queries. They usually placed on columns which are used in search criteria. By default columns are created without indexes because:
- indexes improve query execution speed only on big amounts of data;
- each index consume resource of DB;
- not all columns need them.
Due to these factors we only give ability to customers to select without setting index for each column themselves.
Regards, Andriy