How can I optimize SELECT .. FROM t1, t2 WHERE t1 join t2 ORDER by timestamp DESC LIMIT 10
I have a table which constantly grows. In selects I need only the (chronologically) last inserted x rows. The solution ORDER by timestamp DESC LIMIT x is a poor solution, since it is an O(n) case for the DBMS. Is there an elegant (probably mysql-proprietary) SQL-solution for this ? My solution is an extra table which holds only the last x inserted rows. That means I will delete older rows regularly, so mysql has to scan no more than x records... --Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join from two different databases
-Ursprüngliche Nachricht- Von: c.smart <[EMAIL PROTECTED]> An: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Datum: 18 March, 2001 01:11 Betreff: Re: join from two different databases >Sorry, No Green Beer, No can do that ;-) > >Richard Reina wrote: > >> I am try to do a join with tables that are in two different databases. >> >> I thing I've got the actual SQL syntax down: >> >> my $q = "SELECT i.inv_no, i.inv_date, >> c.cust_name >> FROM receivables.invoice i, sales.customer c, >> WHERE i.paid_date IS NULL"; >> >> but I am confused on how to prepare and execute it, since my database >> handle ($dbh) specifies only 1 database. >> >> Any help would get me out of the office on St. Pats day and in fron tof >> a bar maid serving green beer. Please help make my day. >> I've done this so far in perl, but with no execution plan. Basically it joins only two tables from different hosts, according to a key you must explicitly specify. Also I programmed a complete "order by" and limit (which wasn't very difficult). I cannot release this peace of sh..., but it works for my environment :-) Probably such functionality will be built in mysql in the future. At least Informix and Oracle support "remote-joins" I think... something like this: select * from user:pass@host:db.table1, table2, user:pass@host:db-table3 where ... --Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Alteon + forks
-Ursprüngliche Nachricht- Von: Pedro <[EMAIL PROTECTED]> An: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Datum: 19 March, 2001 17:29 Betreff: Alteon + forks >Hi, > > I have 2 servers and 1 alteon in HA. But the problem is that the alteon , >when trying, to see if the servers are up or down , makes mysql to fork , >till it dies.. >The connection port we are using is 3306 . >Does anyone have any idea? >Thanks in advance Hmm, I don't have an alteon here, but I'm using BigIP from F5, which is similar to alteon products (only better, but that's my opinion :-) My loadbalancer checks also my mysql-dbms's. In particular he will do an tcp-connect to port 3306 and if he succeed, he will close the connection immediately. Probably your alteon isn't closing the connection properly or checks the machines too frequently. So I don't think that your problem is related to mysql. --Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: Innobase in MySQL
-Ursprüngliche Nachricht- Von: Peter Zaitsev <[EMAIL PROTECTED]> An: Andreas Vierengel <[EMAIL PROTECTED]> Cc: Greg Cope <[EMAIL PROTECTED]>; Heikki Tuuri <[EMAIL PROTECTED]>; [EMAIL PROTECTED] <[EMAIL PROTECTED]> Datum: 18 March, 2001 10:35 Betreff: Re[2]: Innobase in MySQL > >Could you tell me what do you mean by index creation ? Did you drop >all indexes or only secondary index while leaving primary in fact ? I've created the table without any index, then did all the inserts and afterwards created all index with one "alter table". --Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innobase in MySQL
-Ursprüngliche Nachricht- Von: Heikki Tuuri <[EMAIL PROTECTED]> An: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Datum: 17 March, 2001 17:35 Betreff: Re: Innobase in MySQL >Andy, > >thank you for your benchmark :). I was also going to measure these, >but have not had time yet. But what parameters you used in >my.cnf? You have a lot of RAM in your system, and you could make >the Innobase buffer pool big. I've tested with the default innobase parameters mentioned in the documentation. > >>1. cat test.sql | mysql innobase => 306 seconds >>2. cat test.sql | mysql innobase, but with autocommit=0 and all inserts >>between BEGIN/COMMIT => 124 seconds > >If you use autocommit = 1, then Innobase has to flush the log to disk after >each individual insert, which makes it slow. It is better to use autocommit = 0, >if you have enough space in your tablespace for the rollback segment. ok, I understand. >>3. same as 1. but with index creation after the inserts. => 264 seconds + >>additional 59 seconds for index recreation. >>4. same as 2. but with index creation after the inserts. => 75 seconds + >>additional 59 seconds for index recreation. > >How do you create the index? With ALTER TABLE? yes, exactly. >>I noticed that in 3. and 4. the machine load was constantly at 100% during >>the inserts. >>but during index recreation the load springs wildley between 0 and 100%, so >>the machine is not maxed out in terms of CPU utilization here (in other > >Innobase has to write to the log which causes some disk i/o, reducing >CPU utilization. Also, when the database has written the log files >full, Innobase has to make a checkpoint, that is, flush written pages >from the buffer pool. You could try making your log files very big, say >150 MB in total, to get a better CPU utilization, and also the buffer pool >bigger, if it is not yet big. ok, I will try the benchmark with tweaked values for innobase again. > >Totally the opposite of spam! I got useful information from this! ok, thanks ! I didn't read the mysql-mailinglist for a while, so I feared "RTFM" as a reply to my posting :-) --Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innobase in MySQL
I've benchmarked a few scenarios to load mysqldumped data into an innobase-table (about 27 columns, mostly int's and float's) with about 150.000 rows with primary key and an additional unique key. The mysqldumped data was generated one insert per row (not dumped with --extended-inserts). 1. cat test.sql | mysql innobase => 306 seconds 2. cat test.sql | mysql innobase, but with autocommit=0 and all inserts between BEGIN/COMMIT => 124 seconds 3. same as 1. but with index creation after the inserts. => 264 seconds + additional 59 seconds for index recreation. 4. same as 2. but with index creation after the inserts. => 75 seconds + additional 59 seconds for index recreation. I noticed that in 3. and 4. the machine load was constantly at 100% during the inserts. but during index recreation the load springs wildley between 0 and 100%, so the machine is not maxed out in terms of CPU utilization here (in other words, the machine is waiting for something to do :-). The same applies to 2. when commit() was send. I tested also 1. and 3. with a myisam-table. Index recreation draws constantly 100% load from the machine here, therfore it's faster. results: 5. same as 1. but with myisam type => 116 seconds 6. same as 3. but with myisam type => 65 seconds + additional 33 seconds for index recreation. system is a celeron 466 / 66MHz FSB, 512 MB RAM, Linux 2.4.2, mysql-3.23.35 If the numbers are not useful to anybody, think of this posting as spam and forget myself... --Andy /"\ \ / ASCII RIBBON CAMPAIGN X AGAINST HTML EMAIL / \ AND POSTINGS - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access Ports
-Ursprüngliche Nachricht- Von: Van <[EMAIL PROTECTED]> An: Andreas Vierengel <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]>; [EMAIL PROTECTED] <[EMAIL PROTECTED]> Datum: 30 January, 2001 16:30 Betreff: Re: Access Ports >Andreas Vierengel wrote: >> >> Try option --skip-grant-tables to mysqld or in mysql.cnf. >> This will dectivate ALL authorization-checking, but will make the server a >> little bit faster... >> >> --Andy >> >Andy: > >Not very secure, I'd argue. > Yes, I know. Therefore I wrote "ALL" in capitalized letters :-) If authorization checking is a "must have" for you, then I think you have no chance to connect without it. Or run a second mysql-server without authorization on the same data-files (not very efficient)... --Andy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access Ports
Try option --skip-grant-tables to mysqld or in mysql.cnf. This will dectivate ALL authorization-checking, but will make the server a little bit faster... --Andy -Ursprüngliche Nachricht- Von: Van <[EMAIL PROTECTED]> An: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Datum: 30 January, 2001 05:20 Betreff: Re: Access Ports >Aftab Rashid wrote: >> >> Hi, >> >> A friend has told me that there is port on which mysql allows anyone to >> access mysql without authentication, is that right? >> If yes, what is the port number ? >> >> Regards, >> >> -- >> Aftab Rashid >> Manager Systems >> Beaconet >> 164 P, Gulberg II, >> Lahore, Pakistan >> +92-42-11122 >> >> http://www.beaconet.net >Aftab: > >To my knowledge, such a port doesn't exist. > >Why would anyone want such access without authentication? > >Just curious. > >Van >-- >= >Linux rocks!!! http://www.dedserius.com >= > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
