StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql search table optimization for a game website
text
Body
copied!<p>I am having difficulties because of the database structure on a game website similar to <a href="http://box10.com" rel="nofollow">http://box10.com</a>. I attempted to change table structure a bit but I made it worse, and now I must not do another mistake to keep my client. So I am scared to make any change. If you can help me I will be extremely appreciated. I didn't want to write my attemts and my thougts on configuration not to take more of your time.</p> <p>There are two tables related with my question. </p> <pre><code> CREATE TABLE `_games` ( `id` INT(6) NOT NULL AUTO_INCREMENT, `title` VARCHAR(100) NOT NULL, `perma` VARCHAR(100) NOT NULL, `approve` TINYINT(1) NOT NULL DEFAULT '0', `tags` MEDIUMTEXT NOT NULL, `description` MEDIUMTEXT NOT NULL, INDEX `id` (`id`), INDEX `approve` (`approve`), FULLTEXT INDEX `ad` (`title`) ) ENGINE=MyISAM ROW_FORMAT=DEFAULT AUTO_INCREMENT=15000 CREATE TABLE `_searches` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `term` VARCHAR(200) NOT NULL DEFAULT '', `viewcount` INT(10) NOT NULL DEFAULT '0', `date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=450000 </code></pre> <p><strong>Usage of _searches table:</strong> </p> <p>On search pages, I insert the search term to _searches table if the term doesn't exists. If it exists, then I update the date and viewcount.</p> <pre><code>$date = date("Y-m-d G:i:s"); $c = mysql_query("SELECT id FROM _searches WHERE term='$term'"); if (mysql_num_rows($c) == 1) { mysql_query("update _searches set viewcount=viewcount+1,date='$date' WHERE term='$term'"); } else { mysql_query("insert into _searches (term,viewcount,date) values ('$term',1,'$date')"); } </code></pre> <p><strong>When I use codes above, mysqld CPU usage goes up to around %500 from %50 and load goes up to 7 from 0.7.</strong> Can you please share some suggestions to help me? </p> <p><strong>Usage of _games table:</strong> </p> <p>On tag pages, I use following sql to get records: (e.g. y8.com/tags/Action)</p> <pre><code>SELECT title,perma FROM _games WHERE tags LIKE '%action,%' AND approve=0 order by id desc LIMIT 0, 20 </code></pre> <p>On search pages, to search games:</p> <pre><code>SELECT perma,title, MATCH(title) AGAINST('$term') AS sort FROM _games WHERE MATCH(title) AGAINST('$term' IN BOOLEAN MODE) and approve=0 ORDER BY sort DESC limit 10 </code></pre> <p>On "related games" sections of game pages: (e.g. <a href="http://www.oyunlar1.com/online.php?flash=5661" rel="nofollow">http://www.oyunlar1.com/online.php?flash=5661</a>)</p> <pre><code>SELECT perma,title, MATCH(title) AGAINST('mario kills the bad guy') AS sort FROM _games WHERE MATCH(title) AGAINST('mario kills the bad guy' IN BOOLEAN MODE) and approve=0 ORDER BY sort DESC limit 10 </code></pre> <p>On home page to get records:</p> <pre><code>SELECT title,perma FROM _games where approve=0 order by id desc LIMIT 0,28 </code></pre> <p>On game pages to get game information:</p> <pre><code>SELECT title,description FROM _games WHERE perma='mario-kills-the-bad-guy' AND approve=0 </code></pre> <p><strong>my.cnf file:</strong></p> <pre><code>[mysqld] set-variable=local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Performance optimization skip-external-locking max_connections = 300 key_buffer = 8M sort_buffer = 1M join_buffer_size = 256K max_allowed_packet = 1M thread_stack = 128K thread_cache_size = 2 table_cache = 1024 thread_concurrency = 2 query_cache_limit = 128k query_cache_size = 4M # InnoDB optimization innodb_file_per_table innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 30 innodb_thread_concurrency = 2 innodb_locks_unsafe_for_binlog = 1 innodb_table_locks = 0 innodb_log_file_size = 2M innodb_buffer_pool_size = 128M # !!!! do not change next 2 values !!!! # data will get destroyed unless you backup everything before changing and then import it back. innodb_additional_mem_pool_size = 32M innodb_log_buffer_size = 256k # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid </code></pre> <p><strong>Server:</strong></p> <p>CentOS 5 (64-bit) (CEN564) Plesk 9.5</p> <p>Quad-Xeon 3220 / 8GB memory / 2x250GB SATAII / 10TB BW / 1GiGE / 8 IPS (SoftLayer)</p> <p>MySQL 5.0.77</p> <p>Server receives around 500,000 pageviews a day.</p> <p>I am open to every kind of suggestion, thank you for your time.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload