err-report help me

Brent Baisley · *nix forums Guru Wannabe Joined: 10 Feb 2005 Posts: 122

MySQL doesn't index the special characters, some of which are considered word separators, like the period. MySQL indexes words, not
strings (sort of).
I had a similar problem on one of my projects where I needed to index resumes for searching. Typical searches would include C++, C#,
..NET, etc. None of which mysql would index.
What I did was created a word conversion list and added a search words field to the database. The search words field would contain
the problematic search words into strings that MySQL would index (CPlusPlus, CSharp, dotNET, etc). The full text index would then be
a compound index of the original text plus the search words field.
The front end would handle searching the text for conversion words before inserting/updating the database. It would then convert the
problematic words that were present and populate the search words field with them. The front would also do a similar conversion when
a user entered a search for one of those words.
It's not perfect, but I always used boolean full text searching and MySQL does support proximity searches, so it worked well enough.
Although I never did figure out how to search on C, J, or any other single character programming language.

Hope that gives you some ideas.

----- Original Message -----
From: "Harini Raghavan" <harini.raghavan@insideview.com>
To: <mysql@lists.mysql.com>
Sent: Thursday, July 06, 2006 2:06 AM
Subject: special char in full-text search

James Harvard · *nix forums addict Joined: 12 Dec 2005 Posts: 56

Although I know nothing about C I imagine this is because the 'type' column can contain all the possible values from an ENUM or SET field.
James Harvard

At 10:30 am +0000 6/7/06, SciBit MySQL Team wrote:

SciBit MySQL Team · *nix forums beginner Joined: 20 Dec 2005 Posts: 3

While you are not wrong, James, is the length member suppose to denote the maximum length of data contained in result's specified column. NOTE: The result's. I.e. why give such an arbitrary number of bytes/length when no ENUM's or SET's are even in the result. The point being, even if you create a table containing 10 INT columns, the result of "show columns from" should show the Type column length of 3 with a maximum data allocation for the 10 rows of 30 bytes, and not ~2MB, as is currently the case.

And even, in a worse case, MySQL Dev decided to give the length back as the maximum "potential" length, who determined 196605 should be the "magic number"? An ENUM can have 64K values, each of which can be a text value/label of at least 64 characters, thus a "magic number" should be at least megs in size to "play it safe". Thus, such an approach is simply put, stupid.

Ideally, as was the case in previous versions of MySQL, the Type column's Length should be given in context of the result, i.e. if there is an ENUM in the column list and it has the longest "type" description, the Type column's length should reflect its contained data size.

Kind Regards
SciBit MySQL Team
http://www.scibit.com

James Harvard · *nix forums addict Joined: 12 Dec 2005 Posts: 56

OK, fair enough. In that case I would think that filing a report on bugs.mysql.com would be your best way forward.

At 8:32 am +0000 7/7/06, SciBit MySQL Team wrote:

Russell Horn · *nix forums beginner Joined: 25 May 2005 Posts: 10

Folks,

I'm trying to import a sql dump of a database which is taking an age.
I've disabled foreign key constraints, unique checks and set autocommit
to 0 but this is still slow.

My data file has a number of tables, one of which has circa 3.5 million
tuples taking up about 500MB of data with 900MB of indexes. This seems
to be where we are slowing down. Most the other tables are much smaller.

The server is a Sun T2000 with 6 cores and 8GB of RAM. We're using the
local disks.

I'm using the mysql.conf file from
http://media.zilbo.com/img/feh/mysql/my.cnf though I've increased
innodb_buffer_pool_size to 3G

Is there anything else I can do to speed up these operations, or should
I resign myself to the import taking several hours each time it's
required?

TIA,

Russell

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Jacob, Raymond A Jr · *nix forums beginner Joined: 21 Mar 2005 Posts: 27

I started the operation below on Friday at 1300hrs EST
DELETE data
FROM data, event
WHERE data.cid = event.cid
AND event.timestamp < "2006-05-01"

It is now Sunday 22:00hrs EST and the operation is still running.

Question: Should it take this long to delete 7.5 million records from a
4.5GB
Table?

Question: Other than writing a script to export all the cid's to a file
and deleting the records one at a time so at least I can delete some
records.
Is there a way to delete records one at a time or in groups
so that if I have to stop the operation the delete will not rolled back?

Question:Does anyone on the list have experience deleting what I guess
is a large number of
records from a large table? i.e. how long does it take?

r/Raymond

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Brent Baisley · *nix forums Guru Wannabe Joined: 10 Feb 2005 Posts: 122

I've had to delete large amounts of records from tables with over 200 millions records. You are correct in that you want to do it in
chunks. How you divide the deletes is up to you to decide. You would want to do it based on a indexed column, like a date, and
specify a range.
DELETE data
FROM data, event
WHERE data.cid=event.cid
AND event.timestamp between "2006-01-01" and "2006-01-07"

DELETE data
FROM data, event
WHERE data.cid=event.cid
AND event.timestamp between "2006-01-08" and "2006-01-15"
....

You'l find it will go much, much quicker if you can narrow down the number of records deleted at one time. A delete that I would
give up on after a few hours take a few minutes by doing it in chunks.

BUT, if your data is always going to be divided into date chunks where you'll periodically delete everything prior to a certain
date, you may want to look into merge tables. Typically a merge table is a psuedo table that is comprised of a set of underlying
tables. You can add/drop underlying tables very quickly, or you could even reference an underlying table directly for certain tasks.
Under the right circumstances, merge tables can provide considerable amount of scalability.

----- Original Message -----
From: "Jacob, Raymond A Jr" <raymond.jacob@navy.mil>
To: <mysql@lists.mysql.com>
Sent: Sunday, July 09, 2006 10:36 PM
Subject: How does one speed up delete-Again

I started the operation below on Friday at 1300hrs EST
DELETE data
FROM data, event
WHERE data.cid = event.cid
AND event.timestamp < "2006-05-01"

It is now Sunday 22:00hrs EST and the operation is still running.

Question: Should it take this long to delete 7.5 million records from a
4.5GB
Table?

Question: Other than writing a script to export all the cid's to a file
and deleting the records one at a time so at least I can delete some
records.
Is there a way to delete records one at a time or in groups
so that if I have to stop the operation the delete will not rolled back?

Question:Does anyone on the list have experience deleting what I guess
is a large number of
records from a large table? i.e. how long does it take?

r/Raymond

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Jacob, Raymond A Jr · *nix forums beginner Joined: 21 Mar 2005 Posts: 27

Jacob, Raymond A Jr · *nix forums beginner Joined: 21 Mar 2005 Posts: 27

Willy Mularto · *nix forums beginner Joined: 31 Mar 2005 Posts: 5

Hello,
I have solved this problem, thanks.

Regards

Willy
----- Original Message -----
From: Willy
To: mysql@lists.mysql.com
Sent: Wednesday, July 12, 2006 8:55 AM
Subject: datetime issue on MySQL 4.x

Hello,
I have a MyISAM table:

CREATE TABLE `dlr` (
`msisdn` varchar(20) NOT NULL default '',
`source` varchar(20) NOT NULL default '',
`operator_id` varchar(20) NOT NULL default '',
`sms_message` longtext NOT NULL,
`smsc_id` varchar(20) NOT NULL default '',
`sms_id` varchar(250) NOT NULL default '',
`dlr_smsc` text NOT NULL,
`dlr` tinyint(4) NOT NULL default '0',
`date_time` datetime NOT NULL default '0000-00-00 00:00:00'
) TYPE=MyISAM

I have a case here, on July 7 I imported some data into the table with this query:

INSERT INTO dlr (source, msisdn, operator_id, sms_message, smsc_id, sms_id, dlr_smsc, dlr, date_time)
VALUES
('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW()),
('366508', '1234', 4, 'id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:xxxx, 'IM33665', MD5(RAND()), 'RE:id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:xxxx', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, 'id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:xxxx', 'IM33665', MD5(RAND()), 'RE:id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:Silahkan tawar: XDA ', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, 'id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:xxxx', 'IM33665', MD5(RAND()), 'RE:id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:xxxx', '2', '2006-06-28 03:17:00'),
('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW()).......

And when I try to fetch the data using PHP with this query:

SELECT operator_id, DATE_FORMAT(date_time,'%d-%m-%Y') AS tanggal, COUNT(*) AS TOTAL FROM dlr WHERE 1=1 AND dlr = 8 AND MONTH(date_time) = MONTH(NOW()) GROUP BY tanggal, operator_id ORDER BY date_time

The weird thing happened. Why does all the rows are shown or treated as July 7th data? If we see the imported data, there should be June 28, June 29.....until July 7. Please help and many thanks for any reply.

Regards

Willy

sheeri kritzer · *nix forums Guru Wannabe Joined: 19 Oct 2005 Posts: 243

create view v_authornames as
select authorid, CONCAT(lastname,',',firstname)
from t_authors;

Or replace the middle term in the CONCAT function to whatever you want
to separate it -- ie, ' ' for a space, or just
CONCAT(lastname,firstname) to get output "KritzerSheeri".

-Sheeri

On 6/25/06, Andreas Bauer <andreas_bauer@arcor.de> wrote:

<fleet@teachout.org> · *nix forums beginner Joined: 16 Jul 2006 Posts: 3

SHORT SYNOPSIS:
The statement
INSERT INTO table (blob_column) VALUES
(load_file('/home/somebody/image.jpg'));

Produces no error; but fails to load the image file.

SOLUTION:
The image file (or other binary file, I assume) MUST reside in / or /tmp,
ie /image.jpg or /tmp/image.jpg. (At least in MySQL 3.23.36)

I *hope* this is a bug!

- fleet -

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Marc Alff · *nix forums beginner Joined: 12 Mar 2006 Posts: 3

Hi Fleet

fleet@teachout.org wrote:

<fleet@teachout.org> · *nix forums beginner Joined: 16 Jul 2006 Posts: 3

On Mon, 17 Jul 2006, Marc Alff wrote:

Dilipkumar · *nix forums beginner Joined: 08 May 2006 Posts: 18

Hi,

If you have like insert statments try using this as

login into mysql
mysql -u root -p
use database-name

\. /tmp/filename.txt

Thanks & Regards
Dilipkumar
----- Original Message -----
From: "Marc Alff" <marc.alff@comcast.net>
To: <fleet@teachout.org>
Cc: <mysql@lists.mysql.com>
Sent: Monday, July 17, 2006 11:21 PM
Subject: Re: load-file() doesn't work [SOLVED]

Google