Efficiency question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Efficiency question

Post 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?
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

Won't make any difference because MySQL does it quick anyway :lol:
User avatar
~J~R~R
Forum Newbie
Posts: 20
Joined: Wed Sep 18, 2002 12:19 pm
Location: Amsterdam, the Netherlanda

Post by ~J~R~R »

Only get the collumns you really need, it's always faster.
User avatar
AVATAr
Forum Regular
Posts: 524
Joined: Tue Jul 16, 2002 4:19 pm
Location: Uruguay -- Montevideo
Contact:

SQL

Post by AVATAr »

More data you request, more processing it will takes..
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post 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.
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post 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. :twisted:

Cheers,
BDKR
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
Post Reply