Thanks for your respond.
Here is the CREATE TABLE:
CREATE TABLE `results_1` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(255) collate cp1251_bulgarian_ci default NULL,
`fileext` varchar(10) collate cp1251_bulgarian_ci default NULL,
`username` varchar(16) collate cp1251_bulgarian_ci default NULL,
`filesize` float default NULL,
`date` datetime default NULL,
`is_dir` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `results_1_filesize` (`filesize`),
KEY `results_1_username` (`username`),
KEY `results_1_filename_1` (`filename`),
FULLTEXT KEY `results_1_filename` (`filename`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_bulgarian_ci
More details:
SELECT count(*) FROM results_1;
+----------+
| count(*)
+----------+
| 2399315
+----------+
1 row in set (0.03 sec)
And some sample data:
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857845, '04 - Iron Maiden - Iron Maiden.mp3',
'mp3', 'drawer', '7.6546e+06', '2006-03-05 01:28:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857844, '03 - Iron Maiden - Fear Of The
Dark.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:29:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857843, '02 - Iron Maiden - Hallowed Be Thy
Name.mp3', 'mp3', 'drawer', '1.25829e+07', '2006-03-05 01:33:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857842, '01 - Iron Maiden - No More Lies.mp3',
'mp3', 'drawer', '1.36315e+07', '2006-03-05 01:31:00', 0);
INSERT INTO results_1 (id, filename, fileext, username, filesize,
date, is_dir) VALUES (857841, '09 - Iron Maiden - Lord Of The
Flies.mp3', 'mp3', 'drawer', '8.70318e+06', '2006-03-05 01:24:00', 0);
And I logged some of the queries which executes for more than 20sec:
query timing: 28.8102879524
SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+*.mp3' IN BOOLEAN MODE) as coef FROM results_1 WHERE MATCH
(filename, username) AGAINST('+*.mp3' IN BOOLEAN MODE) > 0 ORDER BY
coef, filename DESC LIMIT 0, 50
query timing: 36.2581338882
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+007' IN BOOLEAN MODE) > 0
query timing: 31.0913391113
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+ACE-HIGH +MP3' IN BOOLEAN MODE) > 0 UNION ALL
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+ACE-HIGHMP3' IN BOOLEAN MODE) > 0
query timing: 32.1210138798
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+.pdf' IN BOOLEAN MODE) > 0
query timing: 29.8846437931
SELECT id, filename, filesize, date, MATCH (filename, username)
AGAINST('+star +craft +mp3' IN BOOLEAN MODE) as coef FROM results_1
WHERE MATCH (filename, username) AGAINST('+star +craft +mp3' IN
BOOLEAN MODE) > 0 UNION ALL SELECT id, filename, filesize, date,
MATCH (filename, username) AGAINST('+starcraft' IN BOOLEAN MODE) as
coef FROM results_1 WHERE MATCH (filename, username) AGAINST
('+starcraft' IN BOOLEAN MODE) > 0 UNION ALL SELECT id, filename,
filesize, date, MATCH (filename, username) AGAINST('+starcraftmp3' IN
BOOLEAN MODE) as coef FROM results_1 WHERE MATCH (filename, username)
AGAINST('+starcraftmp3' IN BOOLEAN MODE) > 0 ORDER BY coef,
filename LIMIT 0, 50
query timing: 28.6531541348
SELECT count(id) as all_records FROM results_1 WHERE MATCH (filename,
username) AGAINST('+need +somebody +to +love' IN BOOLEAN MODE) > 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebody' IN BOOLEAN MODE) > 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebodyto' IN BOOLEAN MODE) > 0
UNION ALL SELECT count(id) as all_records FROM results_1 WHERE MATCH
(filename, username) AGAINST('+needsomebodytolove' IN BOOLEAN MODE) > 0
I've tried with the MEMORY storage engine, unfortunately it doesn't
support fulltext indexes.
Svilen Spasov
On Jul 12, 2006, at 7:05 PM, John Hicks wrote:
Svilen Spasov (Ancient Media) wrote:
Hello,
I have a website with a MySQL database and I have a table with ~2
millions row (usernames, filenames; ~120MB db data file and ~230MB
db index file) with I would like to search using the fulltext
indeces.
Unfortunately the server get loaded pretty much. It always stays
on 20 load average and often gets 50-60 load average.
I'm sure that this is because of slow mysql response (slow
queries) to the apache web server.
Can you give me some advice how to optimize the mysql server?
The server hardware is: 1GB RAM, 1.8GHz Celeron, 40GB HDD
(currently I have 512MB free memory)
Run a "SHOW CREATE TABLE <tablename>" on the table and post the
results here. (We're particularly interested in how it is indexed.)
Tell us what queries are being run against the table. If possible
post the actual SQL queries.
-J
Thanks,
Svilen Spasov
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
