Query question: select * from table where id in (1,2,3) order by date uses FILESORT

Hi all, this is a problem I'm running into: A table has like 400,000 rows, with a primary key index id. I use this query: SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date DESC LIMIT 0, 40 The problem is, it uses the index id and then a FILESORT. Questions: 1) Am I correct

RE: Aborting a greedy querry from the command line

On Monday 19 June 2006 04:08 pm, Logan, David (SST - Adelaide) wrote: Hi Chris, I've noticed that a Ctrl-C will also leave the query running (5.0.22 - Linux) and I've had to use the kill from mysqladmin or mysql client to get rid of it. Huh, that's odd, it should abort

Re: Aborting a greedy querry from the command line

On Tuesday 20 June 2006 08:23, Ciprian Vizitiu wrote: On Monday 19 June 2006 04:08 pm, Logan, David (SST - Adelaide) wrote: Hi Chris, I've noticed that a Ctrl-C will also leave the query running (5.0.22 - Linux) and I've had to use the kill from mysqladmin or mysql client

RE: Aborting a greedy querry from the command line

Quoting Ciprian Vizitiu [EMAIL PROTECTED]: On Monday 19 June 2006 04:08 pm, Logan, David (SST - Adelaide) wrote: Hi Chris, I've noticed that a Ctrl-C will also leave the query running (5.0.22 - Linux) and I've had to use the kill from mysqladmin or mysql client to get rid

finding the slow query

Hi, I have some troubles finding the slowest queries. My server has regular high loads when a lot of queries that hit the same tables slow down. The question is, which query is slowing it down? The others are probably just slow because the whole thing is slow. And a related question: what's a

Re: finding the slow query

On Tuesday 20 June 2006 08:49, Peter Van Dijck wrote: My server has regular high loads when a lot of queries that hit the same tables slow down. The question is, which query is slowing it down? The others are probably just slow because the whole thing is slow.

How can I show the contents of warning using mysqlimport?

I imported the data of the table using mysqlimport. I used the following command line. $ mysqlimport -ukawabe -paaa -h192.168.1.92 --local kawabe C:\index_test.txt kawabe.index_test: Records: 4 Deleted: 0 Skipped: 4 Warnings: 2 I want to show the contents of warning ,so I

Re: finding the slow query

Hi, Maybe you can use --log-slow-queries. http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html Gu Lei - Original Message - From: Peter Van Dijck [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 20, 2006 3:49 PM Subject: finding the slow query Hi, I have some

About the leftmost index prefixes using nounique index

hello, I want to know about the leftmost index prefixes using nounique index/ About the composite index using primary key, in the reference 7.4.5, I can understand the following: --

Re: finding the slow query

Or try my own tool mymonitor. Available on sourceforge at https://sourceforge.net/projects/mymonitor It's still in beta, but works OK. Still work in progress, and features will be added, but in your situation, I think it could be useful. Best regards Anders Karlsson Duncan Hill wrote: On

mysql-arm cross compilation problem

Hi all, I have download the mysql-4.0.27. And I am trying to cross compile it for Intel-XScale ARM processor on Linux-2.4.20. I have run the configure command as follows # CC=$PATH/arm-linux-gcc LD=$PATH/arm-linux-ld ./configure -C --host=arm-linux This gives the error as follows (last few

Docs team looking for tech writer

Dear all, the MySQL documentation team is looking for a new technical writer to join the team in July or August. We're a small team but spread across the world (Australia, America, Europe). If you're interested in that job, or know someone who is, please drop me an e-mail. Here's an excerpt

i want to speed up data retriving at sql side

Hi All, I need some suggestions from you. I need to program a webbased tool, where i use Tomcat as Webserver Mysql as database. In database i have tables which have rows 300,000 to 500,000. I written but the pages are loading very slowly. What i can do in perspictive of database, so that data

Re: Docs team looking for tech writer

On 6/20/06, Stefan Hinz wrote: The MySQL documentation team is looking for another technical writer. For this we need the best and the most dedicated people around. You may work from anywhere in the world as long as you have the necessary skills and technical facilities to communicate across

Re: Docs team looking for tech writer

Jochem van Dieten schrieb: On 6/20/06, Stefan Hinz wrote: The MySQL documentation team is looking for another technical writer. For this we need the best and the most dedicated people around. You may work from anywhere in the world as long as you have the necessary skills and technical

problem with altering a table

Hi, I'm using dbmail - a program that holds mails in a database, for example mysql. I'm in the process of migrating an old installation to the current one. dbmail does provide a migration script, but I've encountered a problem. It definitely is mysql specific and I haven't yet gotten a

Re: i want to speed up data retriving at sql side

I don't know what your queries look like, but faster disks is always a good start when you need to transfer lots of data. That said, I have found that with MySQL, if I need to process lots of records, it's often quicker to do it in increments rather than in one big block. For instance, if you

Re: finding the slow query

If you do a SHOW PROCESSLIST list, you might see some queries with a state of Locked or Sending Data. The sending data query may be the one that is causing all the others to wait. You would see these state a lot more on MyISAM tables than InnoDB. - Original Message - From: Peter Van

Re: Query question: select * from table where id in (1,2,3) order by date uses FILESORT

MySQL is doing a file sort on the query result. It's not sorting the entire table and it's not sorting the 40 record limit you specified. It's sorting the WHERE id IN... result. After the sort, then it will return just the first 40 records. You can throw and EXPLAIN in front of the query to see

Re: Query question: select * from table where id in (1,2,3) order by date uses FILESORT

I agree with Brent on what MySQL is doing ... are you seeing poor performance with this query? If so, you might evaluate whether adding an index on your 'post_date' column improves things, as MySQL may be able to sort and thus LIMIT more quickly (using index in RAM rather than reading off

Sql optimization

Hi All, I am usingINNODB.I have a delete quetry something like this : delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 ,593540 ) and modnaptrrecord_zone.modnaptrrecord_id not in (593536 ) This is

RE: Mysqlhotcopy / Perl / DBD::mysql on RHEL 4 x86_64

Solved -- After upgrading to 5.0.22, DBD::mysql builds without any problems. -Original Message- From: Tim Lucia [mailto:[EMAIL PROTECTED] Sent: Monday, June 19, 2006 4:17 PM To: mysql@lists.mysql.com Subject: Mysqlhotcopy / Perl / DBD::mysql on RHEL 4 x86_64 I'm running on a Xeon 3.8

Re: Sql optimization

You're hard-coding ten thousand discrete values between the two IN clauses?? I'm not sure how MySQL processes a query like that but I can imagine that the performance would not be good. Frankly, I am: a)suprised that you had the patience to type in all of those values (andverify that you

Problems when compiling the source

Hello all. I have an odd problem i hope you can help me with. I've been compiling mysql from source for the past 10 years or so and just recently thought it mighjt be tiome to upgrade from the 3.23.58 i have, to a newer version. I chose mysql 4.1.20 and went on to compile it. A little info

Copy database to a file

I have been reading the Reference and saw how to convert my database tables and stuff to the words that made them and puts it into a file. But now I need it I can't find it. If you know how please send along how or a page(s) in the Reference. Karl -- MySQL General Mailing List For

Re: intended behavior of host %

Is there a reason that '%' doesn't match 'localhost'? I recently spent more time than I care to admit tearing my hair out over this while setting up authorization for a user. I've looked at the link that James provided, which does at least mention this inconsistency, but it doesn't seem to

RE: Copy database to a file

The command is mysqldump Here's the man file on it: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html -Original Message- From: Karl Larsen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 20, 2006 3:09 PM To: mysql@lists.mysql.com Subject: Copy database to a file I have been

RE: Copy database to a file

I'm running MySQL server 5.0...??? mysql mysqldump --help; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump --help' at line 1 -Original Message- From: J.R. Bullington

Re: Copy database to a file

On Tuesday 20 June 2006 21:37, Thomas Amundsen wrote: I'm running MySQL server 5.0...??? mysql mysqldump --help; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql dump --help' at

Re: Problems when compiling the source

On Tuesday 20 June 2006 12:02 pm, Martin Jespersen wrote: ./configure --prefix=/opt/.mysql-4.1.20 --enable-assembler --enable-thread-safe-client --enable-static=all --with-gnu-ld --with-mysqld-user=mysql --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static

Re[2]: Copy database to a file

Здравствуйте, Thomas. Вы писали 20 июня 2006 г., 23:37:25: TA I'm running MySQL server 5.0...??? mysql mysqldump --help; TA ERROR 1064 (42000): You have an error in your SQL syntax; check the TA manual that TA corresponds to your MySQL server version for the right syntax to use TA near 'mysql

Re: Sql optimization

2006/6/20, [EMAIL PROTECTED] [EMAIL PROTECTED]: Hi All, I am using INNODB.I have a delete quetry something like this : delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 ,593540 ) and modnaptrrecord_zone.modnaptrrecord_id

Good day

Message de la Plate-Forme SECURITE de l'Universite Sidi Med Ben Abdallah de Fes - USMBA - ALERT!!! This e-mail contained one or more infected files. The following attachments were infected and have been repaired: No attachments are in this category. The following infected attachments were

Re: Problems when compiling the source

I thought of that, but i didn't think it'd be a problem since i do have thpse shared libraries for the application to use at runtime - and since the server runs just fine, i doubt that is what is causing the problem (tho it is definately worth trying). It seems to me that if this is truely the

Duplicate Key problem (UPS software)

I'm having a problem dealing with a 3rd-party app that is connecting to my database via ODBC (using the windows connector). This software (UPS Worldship) creates shipment rows and sends INSERT commands on one table. This table has a primary key that should be unique. Each row in the table has

several master for one slaveserver for backup purpose?

Hi, I've been thinking about setting up a slave server for a few of my mysqlservers for extra backup safety. Some of the databases are quite large, about 50gb so a mysqldump is very clumsy to handle. And near to live backups would be nice to have in a panic situation. What I dont know is if

MATCH and return some text

Hi all, is it possible to do a MATCH AGAINST and return some of the text, for example the first paragraph that contains the matching words? Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an

Question about mailing list protocals

I have been subscribed to this list for a couple of years now. I don't often respond and rarely ask questions, but I do read it every day and typically learn something new. Around the 13th of this month I suddenly stopped receiving the mailing list (though I was able to send a question to

Re: Duplicate Key problem (UPS software)

If there is a way for you to capture INSERT commands and convert them to REPLACE commands, you'll have your solution. - Original Message - From: css [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 20, 2006 6:44 PM Subject: Duplicate Key problem (UPS software) I'm

Re: Duplicate Key problem (UPS software)

On Tue, 20 Jun 2006, css wrote: } When someone voids a shipment in the UPS software, it sends another } INSERT to the database, with the same data as the original row, the only } difference being the void column is now Y; } } This of course doesn't work because Duplicate entry 'BLAHBLAH' for

Full-Text problems

Hi all, I am experiencing some issues with Full-Text and was hoping someone could shed some light on the following. I have some content which I know contains for example the word news, the table is MyISAM, the column type is LONGTEXT, there is an index on the column of FULLTEXT. I have

mysql-cross compilation problem

Hi all, I have been trying to cross compile mysql4.0.27 for an Intel-XScale based ARM board on a i686-Linux PC. I have run configure with the following command line options: $ CC=$PATH/arm-linux-gcc LD=$PATH/arm-linux-ld ./configure -C -–build=i686-linux -–host=arm-linux -–target=arm-linux It