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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

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

Post 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
Last edited by Benjamin on Mon Aug 28, 2006 6:13 pm, edited 1 time in total.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

how about count( primary_key_column )? Why count all rows?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Ah I see. Thank you.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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)

GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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).
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Can you do that with a varchar?
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply