Performance issue in using SELECT *? [duplicate]

by

If you need a subset of the columns, you are giving bad help to the optimizer (cannot choose for index, or cannot go only to index, …)

Some database can choose to retrieve data from indexes only. That thing is very very helpfull and give an incredible speedup. Running SELECT * queries does not allow this trick.

Anyway, from the point of view of application is not a good practice.

Example on this:

  • You have a table T with 20 columns (C1, C2, …, C19 C20).
  • You have an index on T for (C1,C2)
  • You make SELECT C1, C2 FROM T WHERE C1=123
  • The optimizer have all the information on index, does not need to go to the table Data

Instead if you SELECT * FROM T WHERE C1=123, the optimizer needs to get all the columns data, then the index on (C1,C2) cannot be used.

In joins for multiple tables is a lot helpful.

More Related Contents:

  1. How does database indexing work? [closed]
  2. Optimise PostgreSQL for fast testing
  3. How many rows in a database are TOO MANY?
  4. How does table alias names affect performance?
  5. Favourite performance tuning tricks [closed]
  6. How to check which locks are held on a table
  7. Two single-column indexes vs one two-column index in MySQL?
  8. How do you interpret a query’s explain plan?
  9. “SELECT *” vs “SELECT ” for performance
  10. Oracle , != , ^= operators
  11. Insert into … values ( SELECT … FROM … )
  12. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  13. Condition within JOIN or WHERE
  14. Subqueries vs joins
  15. IN vs OR in the SQL WHERE Clause
  16. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  17. MySQL not using indexes with WHERE IN clause?
  18. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  19. What are the benefits of using database cursor?
  20. What is semi-join in database?
  21. The backend version is not supported to design database diagrams or tables
  22. SQL query that gives distinct results that match multiple columns
  23. Android multiple databases open
  24. What is it exactly a BLOB in a DBMS context
  25. Composite Primary Keys : Good or Bad?
  26. Is there a version control system for database structure changes?
  27. Using a database table as a queue
  28. Oracle DB: How can I write query ignoring case?
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. SQL Server equivalent of WM_CONCAT function [duplicate]

Leave a Comment