2007. 12. 13, csütörtök keltezéssel 10.14-kor tedd ezt írta: > 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? well, sorting 5000 rows if it has some index is negligible time. so first I would ADD INDEX, then go with my simple query above ;) if that's not possible, e.g. you have read-only access to the database, then you really need a temporary table. the fastest way in that case I think would be something like this: CREATE TEMPORARY TABLE whatever( //fields here ); SELECT * FROM origtable WHERE product_name != '' ORDER BY product_id INTO OUTFILE '/tmp/whatever'; LOAD DATA INFILE '/tmp/whatever' INTO TABLE whatever; and then use table whatever. don't forget to delete the temporary file after loading data, as it is writable by any user on the server ;) greets Zoltán Németh > > Cheers, > > tedd > > > > > > > -- > ------- > http://sperling.com http://ancientstones.com http://earthstones.com > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
