sphinx9999

Cool, thanx fenway

Ah yes, I recon DISABLE KEYS is the way to go, thanks. Incidently, how will locking the tables help? Does this also stop the index update?

I have a large amount of data (20m+ rows) in tableA with no indexes. I am copying this to tableB which has an index on a date field. I am copying it using INSERT INTO tableB SELECT * FROM tableA type syntax. Will mysql update the index once for each row in tableA (boooo) or once at the end of the query (yaaay)? If the former is true then I guess I can delete the index before starting and then recreate it at the end. Thanx

Thanx, When you say drop the transaction do you mean rollback (i.e. the same outcome as rollback)? I could try it but I'm not sure how long I'd have to wait to find out the result... I assume there will be a timeout but dunno when that'll kick in...!

Just wondering, anyone know what would happen if I started a transaction, set off some data-intensive queries and then the db connection is lost...? With no commit or rollback specified what will mySQL do? I want: SET AUTOCOMMIT=0; BEGIN; INSERT INTO logquick_backup SELECT *, "2008-03-18 11:22:23" FROM logquick WHERE logtime < '2008-03-17 23:59:59' ORDER BY logtime ASC LIMIT 5000; COMMIT; SET AUTOCOMMIT=1; but what if MySQL only receives: SET AUTOCOMMIT=0; BEGIN; INSERT INTO logquick_backup SELECT *, "2008-03-18 11:22:23" FROM logquick WHERE logtime < '2008-03-17 23:59:59' ORDER BY logtime ASC LIMIT 5000; ? Thanks

That looked gr8 but unfortunately gives 1248 - Every derived table must have its own alias. I assume this must mean that the outer select must be named... Update: Just added 'as t' and its working fine now! Full query is select avg(counter) from ( select count(*) as counter from logquick group by logtime order by logtime desc limit 60 ) as t; Multi-thanx for your help fnairb

Hi, I'm creating a query that will calculate the average count over the last n (60) seconds. I have got the first part: select count(*) as counter from logquick group by logtime order by logtime desc limit 60; This brings back something like this: counter 8 4 15 6 5 2 2 10 ... 7 2 4 6 6 6 2 2 Any ideas how I can now add in the averaging segment? Currently I am doing this in the PHP but obviously doing it all in one query would be better. select avg(count(*))as counter from logquick group by logtime order by logtime desc limit 60; 1111 - Invalid use of group function Thanx

Which do you think is better, DISTINCT or GROUP BY? SELECT DISTINCT id FROM phrase WHERE campaign LIKE 'proper%' or SELECT id FROM phrase WHERE campaign LIKE 'proper%' GROUP BY advert_id I've asked this question before and received the following answer from a reliable source: "When there's just one column it doesn't matter". Do you agree?

No luck. Just got 6s pause and then the full output "I'm going to Ob in 3 secondsThis is OB OutputI'm out of OB for 3 seconds." ???

nah, you're thinking of usleep!

Yeah, I think buffering is going to be my only hope here - the div solution is good but won't work for my xml formatted page. I'm not sure why my development server is not allowing OBing. It stores the text in the buffer but does not output it seperately from the rest of the text. For example: ob_start(); echo 'test'; ob_end_flush(); sleep(3); echo 'another test'; I would expect 'test' to appear straightaway and then after a 3 second pause 'another test'. However, I just get a three second pause and then both strings appear together. Ho hum...

Unfortunately I cannot use output buffering. I'm really asking if there are any alternatives to it. I know this sounds weird and I'm not sure if it would really work but I've been thinking of something along the lines of echoing out an html javascript tag with the src being another script with the logging functionality in it: echo $ad; echo '<script type="text/javascript" src="logging.php"></script> logging.php: <? //logging functionality here ?> This way all echo's would be performed AND THEN the logging would be done. However, if this works it will only work for an html output - great for my html page but not so good for my xml formatted version... Thanx

I need to output data in two stages: I have an advert displaying script with a logger at the end. I am concerned that if my scripts cannot connect quickly to my logging db for any reason, the advert will not be displayed quickly. I need to force the advert to display, irrespective of whether it is logged. I know I should be able to this with output buffers (flush after echo $ad, then log) but this option is not available to me. Any other ways?

Why do you almost never want distinct? I'm trying to find out which is best (performance-wise) - group by or distinct. seems to be a big debate on this with most people saying they are both identical. your comment suggests you think there is a difference. i.e. SELECT DISTINCT id FROM phrase WHERE campaign LIKE 'proper%' versus SELECT id FROM phrase WHERE campaign LIKE 'proper%' GROUP BY advert_id

Excellent solution - was hoping there would be a simple function to perform this. It even works with a built-in function: $arr=array_map('intval',$arr); Thanks Thanks too - I hadn't any idea that an array could be manipulated directly in a foreach like that (even though it makes complete sense looking back...). Will certainly use this in future.