MySQL Forums
Forum List  »  Performance

Re: Mysql Speed Optimization
Posted by: Ulf Wendel
Date: April 25, 2005 02:19AM

C�sar Couto wrote:
> How much different in speed is...
> 1 - SELECT * FROM ....
> 2 - SELECT id_foto, img1, img2 FROM...

Ahoy!

It depends very much on the query and your schema definition. And that's a general problem with your question: no schema given.

The major difference between the two queries that you mention is:

- the amount of data returned
- index scan vs. full table scan

SELECT * FROM t1 forces the database to read all the columns from the table t1. If the column has 100 columns and your application is only interested in 20 column then the database has:

- to read and process the data from 80 unwanted columns
- transfer the data of 80 unwanted columns from the server to the client

Depending on the internal storage structures the read (I/O) overhead is more or less important. But it depends of course on column types and their storage requirements. If the 80 unwanted columns are contained in span-records that go over several pages, then you'd cause extra I/O to fetch these pages from disk. If you would be using MaxDB and there's some BLOB columns among the 80 unwanted columns, then there would be additional I/O due to the access of extra B*-trees used for BLOB storage. So, generally speaking: never force any system to do more work than required no matter if it's a database or your application or...

Once you've fetched the unwanted 80 columns it's required that you transfer the data from the server to the client. Usually the clients are connected via a network. More data means more network I/O. Generally speaking networks are slow. Again: never transfer more data than really required.

A SELECT * FROM t1 query requires a full table scan. That means the database has to read every row in the table. This is a huge overhead if you compare it to an index scan that's appliable if you have a query like SELECT idx_column FROM t1. The basic idea: if a query does contain only columns from an index (primary or secondary), then it can be performed by an index scan. The usage of can index can be much faster than a full table scan - but it can be, it must not be faster. Anyway, quite often an index is helpful.

Note: these are general notes for most databases. I did not discuss how MySQL or MaxDB in particular will handle one or the other query.

Ulf

Options: ReplyQuote


Subject
Views
Written By
Posted
5966
April 19, 2005 05:17AM
2424
April 24, 2005 04:03PM
Re: Mysql Speed Optimization
2264
April 25, 2005 02:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.