Re: Mysql Speed Optimization

[Ulf Wendel]

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