Re: Boolean searches on InnoDB tables?
At 02:54 AM 3/4/2006, Daevid Vincent wrote:
I just discovered this:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Which states:
They can work even without a FULLTEXT index, although a search executed in
this fashion would be quite slow.
But then I'm kicked in the nuts because:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Full-text indexes can be used only with MyISAM tables
When I try a query on an InnoDB table:
SELECT * FROM categories WHERE MATCH (name) AGAINST ('+ELECTRONICS' IN
BOOLEAN MODE);
I get:
Error Code : 1214
The used table type doesn't support FULLTEXT indexes
So, what is the deal? Am I missing something?
And if I can't use boolean searches on InnoDB tables with mySQL 5.0.18,
Then WHEN will I be able to?
In the mean time, what is the best way to generate this equivallent
functionality via PHP or some other mySQL 5 sanctioned way? I've seen
several different examples on the web, but don't know which to commit to.
Daevid,
Get yourself an ice-pack and visit http://www.sphinxsearch.com/.
They have a free full text search add-on for MySQL that works with InnoDb
and MyISAM tables. You'll feel better in the morning. :)
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Boolean searches on InnoDB tables?
*MySQL Manual - Chapter 12.7.4. Full-Text Restrictions* says: *Full-text searches are supported for MyISAM tables only. * You could try to do what i did... with some overhead... I also had InnoDB tables for an application and also was in a great need of Full-Text Searches. I made a mirror MyISAM table but with the full text index. The full text searches were performed on the MyISAM tables. The inserts were done on both tables. Now depending on the size of the tables you have to do a sync. of the tables or complete reconstruction of the MyISAM table. Despite the fact that inserts were done on the both tables I also did a sync. every Sunday ( I had the smallest traffic on Sundays) and a complete reconstruction of the table every 2 months... ( MyISAM table - is now 750 MB ) The system is working fine for about 16 months now ! -- Gabriel PREDA Senior Web Developer
RE: Boolean searches on InnoDB tables?
Osku is working on FULLTEXT for InnoDB. So, despite what the documentation says: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html Which states: They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow. You're saying that InnoDB tables cannot use a BOOLEAN search at all, no way, no how? At the moment I only have a small db while I'm building the product, so slow is going to be relative at this point (ie. fast), and I would expect it to converge when you get FULLTEXT working in InnoDB. I would like to get the code in there though for now. Do you have an estimate timeframe till this is implemented? Weeks? Months? Version? You could look at Sphinx, for exapmple. What is Sphinx? I did a quick google search but found many references to pyramids and other projects (popular name). Do you have a URL for this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Boolean searches on InnoDB tables?
Daevid, - Original Message - From: Daevid Vincent [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 07, 2006 5:54 AM Subject: RE: Boolean searches on InnoDB tables? Osku is working on FULLTEXT for InnoDB. So, despite what the documentation says: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html Which states: They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow. You're saying that InnoDB tables cannot use a BOOLEAN search at all, no way, no how? hmm... I have not heard that a MATCH ... AGAINST query in MySQL could work without a FULLTEXT index. If you try the query on a MyISAM table that does NOT have a FULLTEXT index, does it work there? At the moment I only have a small db while I'm building the product, so slow is going to be relative at this point (ie. fast), and I would expect it to converge when you get FULLTEXT working in InnoDB. I would like to get the code in there though for now. Do you have an estimate timeframe till this is implemented? Weeks? Months? Version? Sorry, no. You could look at Sphinx, for exapmple. What is Sphinx? I did a quick google search but found many references to pyramids and other projects (popular name). Do you have a URL for this? http://www.shodan.ru/projects/sphinx/ Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Boolean searches on InnoDB tables?
Daevid,
- Original Message -
From: Daevid Vincent [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Saturday, March 04, 2006 9:54 AM
Subject: Boolean searches on InnoDB tables?
I just discovered this:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Which states:
They can work even without a FULLTEXT index, although a search executed
in
this fashion would be quite slow.
But then I'm kicked in the nuts because:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Full-text indexes can be used only with MyISAM tables
When I try a query on an InnoDB table:
SELECT * FROM categories WHERE MATCH (name) AGAINST ('+ELECTRONICS' IN
BOOLEAN MODE);
I get:
Error Code : 1214
The used table type doesn't support FULLTEXT indexes
So, what is the deal? Am I missing something?
And if I can't use boolean searches on InnoDB tables with mySQL 5.0.18,
Then WHEN will I be able to?
Osku is working on FULLTEXT for InnoDB.
In the mean time, what is the best way to generate this equivallent
functionality via PHP or some other mySQL 5 sanctioned way? I've seen
several different examples on the web, but don't know which to commit to.
You could look at Sphinx, for exapmple.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Boolean searches on InnoDB tables?
I just discovered this:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Which states:
They can work even without a FULLTEXT index, although a search executed in
this fashion would be quite slow.
But then I'm kicked in the nuts because:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Full-text indexes can be used only with MyISAM tables
When I try a query on an InnoDB table:
SELECT * FROM categories WHERE MATCH (name) AGAINST ('+ELECTRONICS' IN
BOOLEAN MODE);
I get:
Error Code : 1214
The used table type doesn't support FULLTEXT indexes
So, what is the deal? Am I missing something?
And if I can't use boolean searches on InnoDB tables with mySQL 5.0.18,
Then WHEN will I be able to?
In the mean time, what is the best way to generate this equivallent
functionality via PHP or some other mySQL 5 sanctioned way? I've seen
several different examples on the web, but don't know which to commit to.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
