FullText indices in MySQL.

Post #240,954 by static 1/10/06 11:07:16 PM 1/10/06 11:07:46 PM Reply	FullText indices in MySQL. All those who don't or won't work with MySQL can stop reading now. Okay, does anyone have any experience with MySQL's FullText indices? We've experimented with them a little bit and have been dissatisfied; eventually I wrote a routine and added some tables to do that kind of indexing ourselves. It bothers me that I found it necessary to do this. In fact, it's been rewritten twice because MySQL doesn't have much of a query optimizer, either. It seems some kinds of Inner Joins are not done efficiently. (Changing away from MySQL is not a decision to be taken lightly: it looks like we might be heading to DB2, but that's at least a few months away.) Wade. "Insert crowbar. Apply force." Edited by static Jan. 10, 2006, 11:07:46 PM EST Expand All History
Post #240,955 by admin 1/10/06 11:09:46 PM Reply	Re: FullText indices in MySQL. (Changing away from MySQL is not a decision to be taken lightly: it looks like we might be heading to DB2, but that's at least a few months away.) Bleh. Why wouldn't you use PostgreSQL? Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #240,957 by static 1/10/06 11:14:57 PM 1/10/06 11:23:40 PM Reply	It's a political reason. In the eyes of certain CIEIOs in this company, PostGresQL would have the same disadvantages and reputation as MySQL. i.e. it's free and running on common Dell hardware. :-/ The only reason Oracle is not being considered is because it's far too expensive. That leaves DB2 as the only one in the running. Besides, we need to investigate clustering and PostGreSQL's clustering is an add-on. Or experimental. Wade. "Insert crowbar. Apply force." Edited by static Jan. 10, 2006, 11:23:40 PM EST Expand All History
Post #240,958 by admin 1/10/06 11:18:38 PM Reply	Boggle. So 1) buy the support package and 2), uh, whatever. :-P PostgreSQL certainly doesn't have the same reputation. You can do a full-text query here across a quarter of a million posts in about 4 seconds. And it certainly doesn't have MySQL's optimizer and other squirrelly behaviours. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #240,961 by static 1/10/06 11:26:30 PM Reply	Believe me, I want to try it. But I can't find the time to benchmark our application against our existing database, let alone convert it to another DB and benchmark it against that. Especially one that isn't on the shortlist. And the reputation being perceived has nothing to do with performance. Wade. "Insert crowbar. Apply force."
Post #241,048 by tuberculosis 1/11/06 7:04:38 PM Reply	DB2->Bummer A certain company I may or may not work for invited that product's creators to demo its capabilities and fitness for a certain high volume (lots of data - low transaction rates) application (very complex queries) and they couldn't make it perform. It just doesn't seem to be a particularly well written piece of software. Take it for what its worth. I remain continually dismayed at why people seem to avoid postgres (which - to be fair - probably would have failed in the aforementioned application as well). Its a perfectly serviceable and solid piece of work with great terms. At least if/when it falls over, you've still got enough in your wallet for a down payment on an oracle server (which is quite likely the only thing likely to rescue you at that point). "Whenever you find you are on the side of the majority, it is time to pause and reflect" --Mark Twain "The significant problems we face cannot be solved at the same level of thinking we were at when we created them." --Albert Einstein "This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses." --George W. Bush
Post #241,052 by drewk 1/11/06 7:14:09 PM Reply	And porting from Postgres -> Oracle is likely to be easy Or at least as easy as you're going to get in a DB port. === Purveyor of Doc Hope's [link\|http://DocHope.com\|fresh-baked dog biscuits and pet treats]. [link\|http://DocHope.com\|http://DocHope.com]
Post #241,059 by tuberculosis 1/11/06 7:57:21 PM Reply	I've gone the other way - not too hard - pretty close "Whenever you find you are on the side of the majority, it is time to pause and reflect" --Mark Twain "The significant problems we face cannot be solved at the same level of thinking we were at when we created them." --Albert Einstein "This is still a dangerous world. It's a world of madmen and uncertainty and potential mental losses." --George W. Bush
Post #241,074 by static 1/11/06 10:10:00 PM Reply	PostGreSQL may be on the cards again. It depends on the state of PgCluster. I've decided I Am Going To Find Time To Test This. Clustering is a Requirement on The Checklist, don-cha-know. Currently, PostGreSQL is being glacial at importing my data. :-/ But I shall perservere. Wade. "Insert crowbar. Apply force."
Post #241,086 by admin 1/11/06 10:44:37 PM Reply	For faster COPYs Turn off all indexes, triggers, and constraints on the table. They will all slow down the import. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #241,096 by static 1/11/06 11:36:35 PM Reply	Been trying to figure out how to do that. Now that I've got a whole table in, I can look at pg_dump's output and see how it does it. Ah... that's how to use COPY... I think I'll be writing my own dump tool to do the data migration. Wade. "Insert crowbar. Apply force."
Post #241,090 by broomberg 1/11/06 10:59:58 PM Reply	Copy is slow? From the same box - in direct backend mode? I found it pretty damn fast - about 1/2 as slow as Oracle's SQL/Loader in direct path mode, and Oracle was pushing hardware limits. Or are you doing a copy from the client, which is pretty bad.
Post #241,093 by static 1/11/06 11:24:58 PM Reply	Well, I'm doing a psql < filename.sql My only reference point is MySQL, remember. And I'm still wrangling with data conversion... I've made it quite a bit faster by turning off fsync and giving it more memory (raised work_mem from 1000 to 4096). Wade. "Insert crowbar. Apply force."
Post #241,095 by broomberg 1/11/06 11:32:05 PM Reply	Explain the copy command you are using Show me exactly what you are doing. I would hope you are staying away from CSV and using tab delimited data. And study this page: [link\|http://www.postgresql.org/docs/8.1/interactive/sql-copy.html\|http://www.postgresq...ive/sql-copy.html] Note this text: Do not confuse COPY with the psql instruction \\copy. \\copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \\copy is used.
Post #241,097 by static 1/11/06 11:38:57 PM Reply	I wasn't using COPY at all. I was using INSERT. After all, that's the portable format, isn't it? :-) Besides, that's what MySQL uses for dump-n-restore. I'll be building a migration tool, I think. Wade. "Insert crowbar. Apply force."
Post #241,098 by broomberg 1/11/06 11:50:35 PM Reply	Huh? It provides an excellant tool for very quick data migration. And you whine about the speed of not using it. Makes no sense.
Post #241,100 by static 1/12/06 12:02:20 AM Reply	I didn't know about it. :-) ... until the last few hours. I discovered it literally minutes after the initial post about how slow the import was. Wade. "Insert crowbar. Apply force."
Post #241,101 by broomberg 1/12/06 12:22:59 AM Reply	Oh, OK Learn it. Use it. Let me know if you neeed any help in working with it. The key issue is let the Postgres backend see the files, do not load through the network.

Welcome to IWETHEY!