Page 1 of 1
Efficiency question
Posted: Sat Sep 21, 2002 10:05 am
by Coco
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?
Posted: Sat Sep 21, 2002 10:49 am
by Takuma
Won't make any difference because MySQL does it quick anyway

Posted: Sat Sep 21, 2002 11:43 am
by ~J~R~R
Only get the collumns you really need, it's always faster.
SQL
Posted: Sat Sep 21, 2002 4:25 pm
by AVATAr
More data you request, more processing it will takes..
Posted: Sat Sep 21, 2002 10:08 pm
by mydimension
technically speaking it is faster to list the column names explicitly then to wildcard them, even if you are selecting all the columns. the speed gain is a very very very smallbut in a case like yours you may want to consider listing the column names out.
Posted: Tue Sep 24, 2002 10:14 am
by BDKR
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
Posted: Tue Sep 24, 2002 12:32 pm
by nielsene
Also naming the fields explicity helps if you ever use the other *_fetch_* commands, such as fetch_array, fetch_row to populate a list of variables all at once. The order of variables is the same as the listed fields, even should the schema change slightly.