Re: [PHP] temp tables mysql OT
At 8:34 AM +0100 12/16/07, Jochem Maas wrote: I guess back in day when rocks[tm] were still in vogue you would have had to be much more frugal with the meager cycles at your disposal - we're spoiled for cycles these days :-) I gave up mine when cars came along. To all -- All points well taken guys. I look at tables more like an array now. Thanks for all the input. 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
Re: [PHP] temp tables mysql OT
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? you mean in general? :-P on a serious note I'd go with Richard's analysis - the number of records is next to nothing, indexed or not. so the only thing wrong with your plan of attack is that's it's overkill it seems other than than it's a sound strategy, we're programmers right? we're lazy ... we encourage you to do less :-). I guess back in day when rocks[tm] were still in vogue you would have had to be much more frugal with the meager cycles at your disposal - we're spoiled for cycles these days :-) Cheers, tedd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] temp tables mysql OT
5000 records is chump-change. select * from products where product_name is not null and product name != '' order by product_id On a 5000-record set, this should be screaming fast even with no index UNLESS your server is already drastically overloaded. On Thu, December 13, 2007 9:14 am, 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? 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 -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] temp tables mysql OT
2007. 12. 12, szerda keltezéssel 20.13-kor tedd ezt írta: Hi gang: Another mysql question. 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 greets Zoltán Németh I've spent over an hour looking at How do I set up a temporary table in MySQL? questions via Google with little help. My eyes and brain are blurry -- giving it up for tonight. Anyone have a sample or a clear reference for me to review? Thanks in advance guys. Cheers, tedd PS: Watch someone (maybe everyone) jump on This ain't a MySQL list. -- --- 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
Re: [PHP] temp tables mysql OT
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? 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
Re: [PHP] temp tables mysql OT
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 reeally 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
Re: [PHP] temp tables mysql OT
On Dec 13, 2007 10:14 AM, tedd [EMAIL PROTECTED] 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?
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
?
// Put DB and config includes here.
$sql = SELECT * FROM prod_table WHERE product_name != '' ORDER BY product_id;
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$ssql = INSERT INTO tmp_table(product_id,product_name,val,val2) ;
$ssql .=
VALUES('.$row['product_id'].','.$row['product_name'].','$row['val'].','.$row['val2'].');
mysql_query($ssql);
}
mysql_query(DROP TABLE prod_table);
mysql_query(RENAME TABLE tmp_table TO prod_table) or die(mysql_error());
?
--
Daniel P. Brown
[Phone Numbers Go Here!]
[They're Hidden From View!]
If at first you don't succeed, stick to what you know best so that you
can make enough money to pay someone else to do it for you.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] temp tables mysql OT
On 12/13/07, Robert Cummings [EMAIL PROTECTED] 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 reeally 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
Re: [PHP] temp tables mysql OT
On Dec 13, 2007 10:37 AM, Daniel Brown [EMAIL PROTECTED] wrote:
?
// Put DB and config includes here.
$sql = SELECT * FROM prod_table WHERE product_name != '' ORDER BY
product_id;
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$ssql = INSERT INTO tmp_table(product_id,product_name,val,val2) ;
$ssql .=
VALUES('.$row['product_id'].','.$row['product_name'].','$row['val'].','.$row['val2'].');
mysql_query($ssql);
}
mysql_query(DROP TABLE prod_table);
mysql_query(RENAME TABLE tmp_table TO prod_table) or die(mysql_error());
?
There are much prettier, cleaner, more efficient ways of doing it,
I'm sure, but if you're banging your head off the wall just vying for
a simple solution, MySQL can handle the ~5,001 queries without issue.
;-)
--
Daniel P. Brown
[Phone Numbers Go Here!]
[They're Hidden From View!]
If at first you don't succeed, stick to what you know best so that you
can make enough money to pay someone else to do it for you.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] temp tables mysql OT
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
[PHP] temp tables mysql OT
Hi gang: Another mysql question. 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. I've spent over an hour looking at How do I set up a temporary table in MySQL? questions via Google with little help. My eyes and brain are blurry -- giving it up for tonight. Anyone have a sample or a clear reference for me to review? Thanks in advance guys. Cheers, tedd PS: Watch someone (maybe everyone) jump on This ain't a MySQL list. -- --- 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
Re: [PHP] temp tables mysql OT
tedd wrote: Hi gang: Another mysql question. 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. Should be possible without a temp table I think.. I've spent over an hour looking at How do I set up a temporary table in MySQL? questions via Google with little help. My eyes and brain are blurry -- giving it up for tonight. create temporary table tablename ( ); same as normal but put the 'temporary' in there :) -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
