Re: is 16 gb ram enough for handing mysql database of size 100 gb having myisam as storage engine ?

[Ronald Brown]

here are few sample queries.

search queries.

SELECT content.*, (MATCH (title,og_name) AGAINST ('+free' IN BOOLEAN MODE)) as score FROM content WHERE enabled = 1 AND MATCH (title,og_name) AGAINST ('+free' IN BOOLEAN MODE) HAVING score > 0 ORDER BY uploaders DESC , upload_date DESC LIMIT 0,25

with optional parameters (category ,upload_date , verified )

SELECT content.*, (MATCH (title,og_name) AGAINST ('+free +space' IN BOOLEAN MODE)) as score FROM content WHERE enabled = 1 AND category = '300' AND upload_date >= '2013-10-16' AND verified = '1' AND MATCH (title,og_name) AGAINST ('+free +space' IN BOOLEAN MODE) HAVING score > 0 ORDER BY uploaders DESC , upload_date DESC LIMIT 0,25


recent content queries

SELECT content.* FROM content WHERE enabled = 1 ORDER BY upload_date ASC LIMIT 0,25

with optional parameters (category ,upload_date , verified )

SELECT content.* FROM content WHERE enabled = 1 AND category = '400' AND upload_date >= '2014-04-16' AND verified = '1' ORDER BY upload_date ASC LIMIT 0,25

browse category content queries

SELECT content.* FROM content WHERE enabled = 1 AND category = '200' ORDER BY uploaders DESC , upload_date DESC LIMIT 0,25

with optional parameters (category ,upload_date , verified )

SELECT content.* FROM content WHERE enabled = 1 AND category = '200' AND upload_date >= '2014-04-16' AND verified = '1' ORDER BY size DESC LIMIT 0,25


content pages are pretty straight forward.

SELECT hash,
title,
og_name,
keywords,
files_count,
more_files,
files,
category,
sub_category,
size,
downloaders,
completed,
uploaders,
creation_date,
upload_date,
last_updated,
vote_up,
vote_down,
comments_count,
imdb,
content.verified,
uploader,
content.record_num ,
users.username as username
FROM content
LEFT JOIN users
ON users.record_num = content.uploader
WHERE content.record_num = ? LIMIT 0,1


thanks for your time.