0

I am working on an application which will have approximately 1 million records. To fetch data I have two options:

  1. Join 10-12 tables (indexed) at run time and get the result
  2. Create views from these tables. Query the view at run time instead of joining tables. I think MySQL won't let me use indexes on views.

Currently for testing I have just 10-20 records and both the options are taking similar time. But when the real time data will be loaded, which option would give better performance?

Richa
  • 1
  • 1
  • 1
    "Create views from these tables. Query the view at run time instead of joining tables. I think MySQL won't let me use indexes on views." If you create a View with `ALGORITHM=MERGE` the MySQL optimizer is rewritting the SQL on the view to the actual table(s) which was been used in the `CREATE VIEW` statement meaning the query will can the table(s) indexes. see source https://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html – Raymond Nijland Jul 24 '18 at 13:26
  • "Currently for testing I have just 10-20 records and both the options are taking similar time. But when the real time data will be loaded, which option would give better performance?" You should provide `EXPLAIN query` for both queries. – Raymond Nijland Jul 24 '18 at 13:27
  • I think you should check this out. https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query – MRX Jul 24 '18 at 13:41
  • Thank you for your response. But won't things be different in SQL server and MySQL? SQL server provides indexed views but I think MySQL doesn't allow that. – Richa Jul 24 '18 at 14:24
  • Behind the scenes a VIEW is just a prepared derived table - you trade the simplification of a complex query for some speed loss. How much - depends, you should test. There are test datasets with different sizes, even billions of records. – IVO GELOV Jul 24 '18 at 14:27

0 Answers0