Hi Susan Using a Select * in a query statement is extremely inefficient anyway, I would always recommend that you do a specific field selection for all queries and only extract the data that you actually need. This will ensure that the query runs faster, is more effectivly cached by the server, is faster to access the extracted data and uses a lot less of the system resources.
As a rule of thumb, I always convert select * queries to a more specific query, sometime yielding a speed increase of 70 to 80 percent for really massive tables. If you have to extract more than 15 fields from a table then it needss to be split if possible and data that is duplicated stored in a seperate table and linked to the first table. Also ensure that your index tables are updated as well. If a table is so massive that the query takes a long time to extract the data you can get timeouts or empty results returned. Hope this helps Conrad ----- Original Message ----- From: "Susan Kelly" <[EMAIL PROTECTED]> To: "SQL" <[email protected]> Sent: Friday, December 07, 2007 6:35 AM Subject: SELECT * returning zero records > I've recently acquired a bunch of 4.X CF sites and am upgrading them to > 6.1 (yes, still behind the times). I've run into a problem that I've > never seen before. We're running CF on a Windows 2003 server and MySQL > 5.0 on Linux. > > <CFQUERY name="get" datasource="canterbury"> > SELECT pageid FROM Pages > WHERE approval = 1 > </CFQUERY> > > Returns the appropraite number of records > > When I run > > <CFQUERY name="get" datasource="canterbury"> > SELECT * FROM Pages > WHERE approval = 1 > </CFQUERY> > > The record count is zero. > > In ideas what is going on? Any potential solutions other than replacing * > with all of the field names? > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3012 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
