Mike wrote:
> Hi,
>
> I am trying to make a website where scalability matters. In my quest to
> make my website more scalable I broke down the following SQL statement:
>
> select * from customers limit 100
>
> to:
>
> select id, updated_date from customers limit 100
>
> Then my application would check it's cache to see if it has those
> records available and will hit the database with consequent:
>
> select * from customers where id = 4 or id = 9 or id = 19
>
> Am I really speeding things up by breaking down the SQL statements to
> what's necessary? or is it faster to get everything right at once!
>
Well, first, it's never really a good idea to use "SELECT * FROM" in a
production application, against a table. Tables can (and do) change
from one release to another, and if the layout of the table changes, you
could be looking at having to rewrite your code, especially if it relied
on the order of the columns in the tables. It's always better to
specify the columns that you're looking for, since existing columns
should (at least in theory on a production DB) remain present, though
their order can change sometimes, depending on what the DBA does. :)
Secondly, as far as making your queries more efficient, the only way
that you can really do that is to determine actually how long the
queries are taking. This is relative to the size of the database in
rows, and of course, the data that you're querying against, whether a
table scan is necessary, and all of that. This is the process of
optimizing queries. For a small table, it can be faster sometimes to
just pull all of the records at once (for example, if they're all within
a single page). However, if you're pulling from a large DB, it will be
faster to use smaller queries against it, using well-placed indexes.
Also, you may wish to consider using views if you really like using *
with SELECT... Since this way, you can just depreciate a view and start
using a new one if the underlying columns are changed. :) Also, if you
use views, you can optimize the view's query when it comes time to
change it, which mess less messing around in the application code,
especially if it is a frequently used query.
HTH,
Mike