Re: temp tables mysql OT

On 12/13/07, Robert Cummings <robert@interjinn.com> wrote: > > On Thu, 2007-12-13 at 10:14 -0500, tedd wrote: > > At 12:20 PM +0100 12/13/07, Zoltán Németh wrote: > > >2007. 12. 12, szerda keltezéssel 20.13-kor tedd ezt írta: > > > > I would like to create a temporary table to perform searches. > > >> > > >> From my main table, I need to exclude records that have certain > > >> fields that are null or empty; and then sort the final result. > > > > > >why do you need a temp table for that? > > >select * from blah where not isnull(checkfield) and checkfield <> '' > > >order by someotherfield > > > > Zoltán: > > > > Ok, here's the problem. > > > > I have a table with over 5000 records. > > > > There is no index (not my dB) and the records are not complete. > > > > There is a numeric product_id field, but in the dB this is not in > sequence. > > > > Some records have a product_id, but no product_name. > > > > I need to travel the dB showing each item in > > order (product _id) and excluding those products > > that have no product_name. > > > > That sounds simple enough, but currently for each > > step the entire table gets sorted (unless I'm > > doing it wrong). > > > > I was thinking that I could: > > > > 1. Create a temporary table. > > 2. Sort the table once. > > 3. Remove the records that have no product_name > > 4. And then just travel the temporary table for the duration of the > script. > > 5. Drop the table when done with it. > > > > Now, what's wrong with my thinking? > > This is what you reeeeeeally want IMHO: > > ALTER TABLE theTable ADD INDEX( product_id ); > > Cheers, > Rob. > -- > ........................................................... > SwarmBuy.com - http://www.swarmbuy.com > > Leveraging the buying power of the masses! > ........................................................... > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > tedd, I have multiple tables in a mysql db that have over 1.3 million indexed records, and selecting the latest 60 records is quick as a bunny, so 5000 records should be a piece of cake. Also, there is no guarantee of record order in a result set without an order by clause, which obviously affects sorting. Rob's suggestion should certainly improve performance. David