Efficiency question
Moderator: General Moderators
Efficiency question
if you have a largish table with quite a few collumns, and you want to return the majority of those collumns, is it better to still list them explicitly or is it acceptable to return via wildcard and simply ignore the returned variables that you dont need?
- mydimension
- Moderator
- Posts: 531
- Joined: Tue Apr 23, 2002 6:00 pm
- Location: Lowell, MA USA
- Contact:
Some said above that speed gain is very very very small. This is not 100% true. It really depends on how much data the is being held in the table to begin with. Here are some things to consider.
1) If you just do a select * for example, then as the table grows, the query will slow. Why? Becuase the db has to return every single record in that table.
2) If you explicitly name what you want returned, the performance will increase as the amount of data that needs to returned is less. However, it may still require a worst case scenario full table scan by the db to complete your request.
3) If you use the explain command at the beginning of your query to help you create indexes or an index, in conjunction with where clauses, you can GREATLY improve the performance of the db. I'm talking about incredible speed gains
The biggest reason is that the index resides in memory. If much of what's being searched for can be found in memory, then your query is far faster as it's far less disk bound.
Take a look at the mysql manual on optimizing it's performance. Also look into the explain statement. You may also consider the slow query log as something to check (if you have that option).
We use MySQL where I work and we've seen some incredible performance gains just by adding compound indexes in the right places. Of course it helps when you are also running an U160 SCSI array, dual processor system with 1.5 Gigs of memory on it's own dedicated network between dbs and servers.
Cheers,
BDKR
1) If you just do a select * for example, then as the table grows, the query will slow. Why? Becuase the db has to return every single record in that table.
2) If you explicitly name what you want returned, the performance will increase as the amount of data that needs to returned is less. However, it may still require a worst case scenario full table scan by the db to complete your request.
3) If you use the explain command at the beginning of your query to help you create indexes or an index, in conjunction with where clauses, you can GREATLY improve the performance of the db. I'm talking about incredible speed gains
Take a look at the mysql manual on optimizing it's performance. Also look into the explain statement. You may also consider the slow query log as something to check (if you have that option).
We use MySQL where I work and we've seen some incredible performance gains just by adding compound indexes in the right places. Of course it helps when you are also running an U160 SCSI array, dual processor system with 1.5 Gigs of memory on it's own dedicated network between dbs and servers.
Cheers,
BDKR