[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 512M
max_allowed_packet = 10M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 10M
read_rnd_buffer_size = 40M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
skip-networking
# Replication Master Server (default)
# binary logging is required for replication
#log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Last edited by Benjamin on Mon Aug 28, 2006 6:13 pm, edited 1 time in total.
MySQL Cookbook wrote:COUNT(*) with no WHERE clause is very quick for ISAM or MyISAM tables. For BDB or InnoDB tables, you may want to avoid it; the query requires a full table scan for those table types, which can be slow for large tables. If an approximate row count is all your require and you have MySQL 3.23 or later, a workaround that avoids a full scan is to use SHOW TABLE STATUS and examine the Rows value in the output.
If you are using MySQL 5+ you can add triggers that execute when an INSERT query is performed on `text` that increment a count field in another table called something like `CountCache` and then SELECT `text` FROM `CountCache`. Did you follow that? I'm not sure that was very clear.
select page_id from page where page_title like "%test%";
That query is taking between a few minutes and several hours depending on what is searched for.
I don't understand whether or not the page_title is indexed properly based on this information. If I create a seperate index for it would that speed it up? What is name_title?
This table is about 600mb, so I can load it ram if I buy more ram. It looks like I've got about 560mb ram that could be used on this server...
I'll throw more ram in that box and turn that table into a damn heap table if I have to, but I'm wondering if there is an easier way. What would creating an index on page_title do? It's already indexed, but I don't know if that index is used in the query or not. I don't want to just start making changes to the table, it's very large, data is still being imported, and any changes would take a long time to complete.
Maybe you could weigh up the pros and cons of creating a fulltext index on the page_title column? (Whether you can or not depends on table type, column type).
manual wrote:
MySQL has support for full-text indexing and searching. A full-text index in MySQL is an index
of type FULLTEXT. FULLTEXT indexes can be used only with MyISAM tables; they can be
created from CHAR, VARCHAR, or TEXT columns as part of a CREATE TABLE statement or
added later using ALTER TABLE or CREATE INDEX.
If your table is MyISAM then you can build fulltext index on the column.
There are a couple things you'd have to keep in mind though, if you want the same results as your LIKE query. By default, FULLTEXT indices have a 50% relevance limit. Using a BOOLEAN search gets around that. Also, there is a minimum word length (that you can change in my.cnf) of, I think 3 characters.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.