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
