Most of the queries you listed below probably aren't taking advantage of the full text index. MySQL doesn't consider song.mp3 or
document.pdf words, they would be considered two words because the period is considered a word separator. Keep in mind MySQL indexes
"words" not character strings. Punctuation (ACE-HIGH) and numbers (007) aren't indexed either. Again the hyphen is a word separator.
Also, searching on "ends with" won't use an index. So your *.mp3 won't be able to use the index. That's probably why everything is
taking so long. You might as well be searching on LIKE "%.mp3%", probably would be about just as fast, and you could use a memory
table if you wanted.
----- Original Message -----
From: "Svilen Spasov (Ancient Media)" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, July 13, 2006 9:57 AM
Subject: Re: fulltext search optimization
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
