I would encourage people to use date filters on their searches, as
that could help by eliminating rows prior to the fulltext search.
For backup and recovery of a database this size, with MyISAM tables, I
recommend the included 'mysqlhotcopy' script. Also a mastery of the
mysqldump command will help a lot, and is an alternate method of
achieving backups.
Dan
On 8/18/06, AmirBehzad Eslami <[EMAIL PROTECTED]> wrote:
Dear list,
I'm programming a PHP-driven Search Engine for a newspaper.
Full-text Search with MyISAM Tables, MySQL 4.1.11, PHP 4.3.0
1GB of Text encoded by UTF-8
An average of 1Mbyte Data is inserted to database every day
A common SQL-Query:
SELECT COUNT(*) FROM news_archive
WHERE
MATCH(news_title, news_text) AGAINST('%s' IN BOOLEAN MODE)
1st Question:
Do you recommend any setting to improve the speed?
Is there anything which I should keep in mind?
2nd Question:
Before this project, I was using phpMyAdmin to backup/restore my database. But
today I need to load 1Gb of data into my tables. There are two problems with
the phpMyAdmin method: Time-out and Max Post-size.
Is there a better way to perfrom this task?
Thank you in advance,
Behzad
P.S.
Database Scheme:
-- Table structure for table `news_archive`
--
CREATE TABLE `news_archive` (
`news_id` int(10) unsigned NOT NULL auto_increment,
`news_type_id` int(10) unsigned NOT NULL,
`news_date` mediumint(8) unsigned NOT NULL default '0',
`news_title` text collate utf8_persian_ci NOT NULL,
`news_text` text collate utf8_persian_ci NOT NULL,
`news_file` varchar(100) character set ascii NOT NULL,
PRIMARY KEY (`news_id`),
KEY `news_date` (`news_date`),
KEY `news_type_id` (`news_type_id`),
FULLTEXT KEY `news_title` (`news_title`,`news_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci COMMENT='News
Archive';
-- --------------------------------------------------------
--
-- Table structure for table `news_subtypes`
--
CREATE TABLE `news_subtypes` (
`news_subtype_id` int(10) unsigned NOT NULL,
`news_type_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`news_subtype_id`),
KEY `news_type_id` (`news_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci;
-- --------------------------------------------------------
--
-- Table structure for table `news_types`
--
CREATE TABLE `news_types` (
`type_id` int(10) unsigned NOT NULL auto_increment,
`type_name` varchar(100) collate utf8_persian_ci NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci COMMENT='News
Types';
---------------------------------
Want to be your own boss? Learn how on Yahoo! Small Business.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
