Page 1 of 1

Slow Query > 1 Hour 27 Minutes 59.65 Seconds [56k Warn]

Posted: Mon Aug 28, 2006 1:36 am
by Benjamin
Umm, LOL, any way to speed this up?
mysql> select count(*) as records from text;
+---------+
| records |
+---------+
| 1679000 |
+---------+
1 row in set (1 hour 27 min 59.65 sec)
Here is my.cnf

Code: Select all

[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

Posted: Mon Aug 28, 2006 10:13 am
by Ollie Saunders
InnoDB table type I expect.
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.

Posted: Mon Aug 28, 2006 10:55 am
by wtf
how about count( primary_key_column )? Why count all rows?

Posted: Mon Aug 28, 2006 2:32 pm
by Benjamin
Ah I see. Thank you.

Posted: Mon Aug 28, 2006 6:12 pm
by Benjamin
Ok, well the select count isn't a big deal, but I really need to speed this up. I'd like to get it below 1 second if possible :(

page_title is a varchar 255.

Code: Select all

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?
Image

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...

Image

Posted: Mon Aug 28, 2006 6:16 pm
by Ollie Saunders
change it to char(255) for an instant no effort performance boost.
change it to unsigned int for a big performance boost.

edit: actually no thinking again that might not make much difference, have you tried my previous suggestions?

Posted: Mon Aug 28, 2006 6:20 pm
by Benjamin
ole wrote:change it to char(255) for an instant no effort performance boost.
Would that actually help? Why would that make a difference?
ole wrote:change it to unsigned int for a big performance boost.
I can't do that, it contains text.
ole wrote:have you tried my previous suggestions?
This is a search query, different from the select count.

Posted: Mon Aug 28, 2006 6:25 pm
by Ollie Saunders
This is a search query, different from the select count.
Uh yeah I thought for some reason you were still talking about the count(*) query earlier.
Would that actually help? Why would that make a difference?
No it wouldn't sorry, buying more RAM almost certainly would.

Posted: Mon Aug 28, 2006 6:32 pm
by Benjamin
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.

Posted: Tue Aug 29, 2006 7:31 am
by GM
If you do an

Code: Select all

EXPLAIN SELECT page_id FROM page WHERE page_title LIKE "%test%"; 
it will show you exactly what indexes are being used in the output.

EDIT: Found this in the manual, page 451:
MySQL Manual wrote: The following SELECT statements do not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the
LIKE value is not a constant.
So I think your statement does not use any index, since you've got a wildcard at each end of the string.

Posted: Tue Aug 29, 2006 7:44 am
by Benjamin
Guess I'll make it a heap table then.

Code: Select all


mysql> EXPLAIN SELECT page_id FROM page WHERE page_title LIKE "%test%";
+----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | page  | index | NULL          | name_title |     259 | NULL | 3590774 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)


Posted: Tue Aug 29, 2006 7:51 am
by GM
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).

Posted: Tue Aug 29, 2006 7:54 am
by Benjamin
Can you do that with a varchar?

Posted: Tue Aug 29, 2006 10:08 am
by GM
from the manual (p. 634):
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.

Posted: Tue Aug 29, 2006 10:11 am
by pickle
Yep.

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.