Help me
I have created a table as follows but i was not able to use full text search on
a specific data.
create table racebike (id int auto_increment not null primary key, name
varchar(10), user text,fulltext(name,user));
The table is as follows,
++-+-+
| id | name| user|
++-+-+
| 1 | pulsar | style and speed |
| 2 | Harley | stylish and costly |
| 3 | aprilla | good sports bike|
| 4 | honda | costly and speed|
| 5 | suzuki | nominal and speed |
| 6 | Bullet | stylish and classic |
| 7 | Karizma | style and high cc |
| 8 | Fz | bigger and speed|
++-+-+
The query is
mysql select * from racebike where match (user) against ('speed');
Thanks in advance
Karthik.P.R
Re: MySql uses 98% CPU
At 07:07 AM 11/4/02 -0800, Jeremy Zawodny wrote: On Mon, Nov 04, 2002 at 12:37:15PM +0100, Nicolas MONNET (Tech) wrote: We get the same thing on FreeBSD machines quite often, and it's not related to the actual work being done; MySQL can sit idle but use up all CPU. It happens after a few days/weeks of uptime. That's a known bug in FreeBSD's threading implementation. Disable name resolution and it'll go away. Some details are here: http://jeremy.zawodny.com/blog/archives/000203.html#000203 and http://jeremy.zawodny.com/blog/archives/000264.html#000264 Jeremy -- Jeremy, just wanted to thank you for the pointer to those details... we've actually had this similar problem where mysql will quite often keep itself up at near 100% cpu usage (running two mysql daemons on a dual cpu box)... it does drop into the 70's sometimes, but even at way off-peak times will never drop to the 5-10% usage we see when we restart mysql, until of course it builds back up again. We haven't bothered too much because frankly it just hasn't slowed down the box enough to make it worth investigating, just a minor annoyance when we are looking at top. Now we are off to take a look at your pointers and see if we can finally flush it out... Thanks again. You da man! - 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
last_insert_id questions
I have a message board app I'm using where I'm trying to make the code faster and more efficient than it is now. At times this board will generate a lot of new posts to it very very quickly. In one section where posts are added, after the post is made and inserted into mysql, we need to get the post number, which is an auto-increment field, for the insert just finished. And then we need to use this to update the boards table with stuff like last post and then maybe send an email with a link in it, etc.. I found the last_insert_id section and it looks like this is what I want, where after I do the insert, I can do something like mysql SELECT LAST_INSERT_ID(); My questions are: If I have something like 500 concurrent users is this a safe and reliable way to get that info if 50 of them are posting at the same time? Is there anything that would prevent the select from getting the last id that I need to watch for such as finish statements, etc... What version did this feature start in so I can make sure we can use it. Thanks in advance for any assistance. - 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
mysql4 - order by, desc queries and indexes
I noticed in the changelog for mysql 4.0.2 that it now Use index for ORDER BY in queries of type: SELECT * FROM t WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC Very cool, I need this. Question though, when creating indexes, do we need to somehow specify desc in the index or does mysql use the same index for both asc and desc order by's... - 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: MySql/InnoDB crashes after a Select...order by DESC... LIMIT...
Heikki, does this bug affect all versions or just the -max windows versions... we just installed 4.0.3-beta (unix-freebsd) to do some benchmarking/testing tonite on innodb and this would impact us. Thx. Rafa, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, September 10, 2002 1:46 PM Subject: MySql/InnoDB crashes after a Select...order by DESC... LIMIT... Hello, I am working with mysqld-max-nt under windows 2000 and unsing InnoDb tables. After running the following select: Select cod,nom from grupos where cod '005' order by cod desc limit 1 mysqld crashes. Also it crashes whith limit 10, limit 50, or whatever limit you indicate. However, the following selects run ok: Select cod,nom from grupos where cod '005' order by cod desc (without limit) Select * from grupos where cod ’D01’ order by cod desc limit 1 Select * from grupos where strcmp(cod,’005')0 order by cod desc limit 1 Select * from grupos where cod 005 order by cod desc limit 1 Select * from grupos where cod 5 order by cod desc limit 1 This error only occurs with InnoDb tables and MySql 4.0.3 (with Mysql 3.23.52 it runs ok). With MyIsam tables it runs ok even under mysql 4.0.3 I enclose the file grupos.sql with the table definition and data. thank you for a very detailed bug report. The bug, assertion failure in btr0pcur.ic, happened because InnoDB did not store the cursor position in this case. The cursor needs to move downward in the alphabetical order. I have now pushed the fix to the 4.0 source tree. Thanks in advance, Rafa. P.D: Sorry for my english. Best regards, Heikki Innobase Oy sql query - 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
innodb questions about message board apps
Hi all, I've been reading a bit on the innodb table type for mysql here and on the site and some other board sites and just wanted to ask a quick question or two in general about it, as it relates mostly to message boards and compared to myisam. Is the only gotcha in forum code generally the select count(*) queries? It's the only code I've seemed to see here that has to be dealt with differently by setting a field for a counter or something similar. Or are there other things in general I need to watch out for. Second, the only general thing I can see is an apparent limit on the record size of, I think, something like 32k in fairly recent versions. If there's a post as part of a record that exceeds this limit, does it get truncated (and what fields would get truncated - that specific fields or the last ones in the table), will it crash the server, anyone have any idea of what happens. Or am I reading this all wrong. I'm thinking about trying it out on a friends board for him to take advantage of the row level locking, so I'm also wondering, has anyone run into problems converting back to myisam if it didn't work out to really improve performance. Thx. - 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: building tree view in mysql?
Hi, There are of course several ways of doing it !! I had to do this kind of thing few weeks ago, and I think the easier way of doing it is use a treepath schema in a field of your table +++---+ | id | treepath | name | +++---+ | 1 | 0 - 0 - 1 | parent 1 | | 2 | 0 - 1 - 0 | parent 2 | +++---+ This case : 0 - 1 - 0 : First sub directory file n° 0 0 - 5 - 1 : Fifth sub directory file n°1 0 path0 0 path0-0 1 path0-1 0 path0-1-0 1 path0-1-1 2 path0-2 1 path1 0 path1-0 5 path1-5 etc... It more simple, more scalable, and it is (I guess!!) the simplest way for sorting results as a tree over a HTML format! The way you store data in your DB depends on the way you output data too so it depends on your client! Hope It helps... -Message d'origine- De : Sagi Bashari [mailto:[EMAIL PROTECTED]] Envoy? : jeudi 23 mai 2002 16:16 ? : [EMAIL PROTECTED] Objet : building tree view in mysql? Hi, I'm trying to build a simple categories tree in MySQL. Say I have the following table: CREATE TABLE test ( id int(10) unsigned NOT NULL auto_increment, parent int(10) unsigned NOT NULL default '0', name tinytext NOT NULL, PRIMARY KEY (id), KEY parent (parent) ) TYPE=MyISAM; +++---+ | id | parent | name | +++---+ | 1 | 0 | parent 1 | | 2 | 0 | parent 2 | | 3 | 0 | parent 3 | | 4 | 1 | sub 1 of parent 1 | | 5 | 1 | sub 2 of parent 1 | | 6 | 2 | sub 1 of parent 2 | +++---+ I would like to get all the childs under their parent, like this: +++---+ | id | parent | name | +++---+ | 1 | 0 | parent 1 | | 4 | 1 | sub 1 of parent 1 | | 5 | 1 | sub 2 of parent 1 | | 2 | 0 | parent 2 | | 6 | 2 | sub 1 of parent 2 | | 3 | 0 | parent 3 | +++---+ How can I get MySQL to sort it like that? I tried to use group/order by, but I cannot get it to sort it this way.. Any ideas? Thanks, Sagi - 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
Backing up/Creating new database
Hi, I have database developed on my laptop. What is the quickest way to backup the database and restore it on the server? Can I restore the database as empty database? Howa bout creating SQL statements based on the database structure? Thanks - 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: Backing up/Creating new database
Thnaks . I tried several variation and they are giving me sql syntax error: Mysqldump --no-data database XXX tmp.sql; I also tried mysqldump --no-data database c:\tmp1.sql What is the proper syntax? I also get message outfile disabled? Thanks Paul -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Monday, May 13, 2002 11:32 AM To: [EMAIL PROTECTED] Subject: Re: Backing up/Creating new database PR, Monday, May 13, 2002, 5:47:23 PM, you wrote: P I have database developed on my laptop. What is the quickest way to P backup the database and restore it on the server? Can I restore the P database as empty database? Howa bout creating SQL statements based on P the database structure? mysqldump utility. If you want to backup only the structure of your database you should use mysqldump with --no-data option, look at: http://www.mysql.com/doc/m/y/mysqldump.html P Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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
indexes and desc
When doing a select that is ordered by a column that is indexed and has a limit, does mysql not use the index if the order by is descending? Here's an example: select parent,number,lastpost from posts where topic=1 and board='abc' and approved='yes' order by lastpost desc limit 9,12 My app is a message board and I'm using date descending as the order. One thought I had is to create a new column like desctime and set the value of each post to something like the time value for say 1/1/2025 minus the lastpost time - that should set it far enough out I wouldn't have to every worry about it. Another thought I guess would be to just store the value as a negative value. If it doesn't use the index in descending order, does anyone have any suggestions on my thoughts here or other ideas on how to work around this most efficiently? And are there future plans for indexing on desc order? - 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
msg board with efficient threaded view design?
I was wondering if anyone could point me to a message board (perl or php) that has a very efficient database design to handle threaded views. I know that this is a difficult task under mysql and have seen the threads on recursive queries, but I'd like to find some real life examples rather than start completely from scratch. I'm currently using wwwthreads, but under under really high user loads it can get bogged down because of the number of queries it's making to get all the replies in the proper threaded order. I know UBB and vBulletin don't do threaded views. Has anyone seen one that handles threading reasonably efficiently? - 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: FreeBSD and multiple daemons
I don't know, I would have to check with my progammer on that one to be sure... does this help mysql -V Ver 11.15 Distrib 3.23.40, for unknown-freebsdelf4.2 (i386) [EMAIL PROTECTED] writes: Jerry, Thats a reasonably serious hardware platform, you should not encounter 30 second delays, did you compile mysql yourself or use a pre-compiled binary... Sid -Original Message- From:PR [SMTP:[EMAIL PROTECTED]] Sent:Sunday, August 19, 2001 4:28 PM To: [EMAIL PROTECTED] Subject: FreeBSD and multiple daemons I'm having a problem with my server getting bogged down once in a while and I'm pretty sure it's due to the mysql daemon getting maxed out with what it can do... when this happens there might be anywhere from 100 to 150 processes in the processlist and it might take as much as 30 seconds to return a query via a web browser. When it happens mysql is usually using almost 100% cpu, but load stays around 1.3 to 1.5. I'm running FreeBSD 4.3-Release and mysql 3.23.40 We recently upgraded both because of some of the past comments about the sleeping threads on the list in slightly older versions. The server is dual P3-1G, 1g ram with SCSI drives on single channel raid card. Is there a very stable way to run multiple daemons on FreeBSD? A howto or some such thing that might describe setting it up. I can run the memory up to 4g on the machine if necessary to handle the extra memory requirements. Also, are there changes I'll have to make to my apps that use mysql to deal with this? I searched the list for multiple daemons and didn't find much there, maybe I'm using the wrong keywords to search for. Any pointers or help would be greatly appreciated. Jerry - 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
FreeBSD and multiple daemons
I'm having a problem with my server getting bogged down once in a while and I'm pretty sure it's due to the mysql daemon getting maxed out with what it can do... when this happens there might be anywhere from 100 to 150 processes in the processlist and it might take as much as 30 seconds to return a query via a web browser. When it happens mysql is usually using almost 100% cpu, but load stays around 1.3 to 1.5. I'm running FreeBSD 4.3-Release and mysql 3.23.40 We recently upgraded both because of some of the past comments about the sleeping threads on the list in slightly older versions. The server is dual P3-1G, 1g ram with SCSI drives on single channel raid card. Is there a very stable way to run multiple daemons on FreeBSD? A howto or some such thing that might describe setting it up. I can run the memory up to 4g on the machine if necessary to handle the extra memory requirements. Also, are there changes I'll have to make to my apps that use mysql to deal with this? I searched the list for multiple daemons and didn't find much there, maybe I'm using the wrong keywords to search for. Any pointers or help would be greatly appreciated. Jerry - 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
