473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

MySQL full-text SELECT COUNT(*) slow

I run a query on a 3 million rows table (avrg row length 2988) as
below:
SELECT COUNT(*) FROM tbl WHERE MATCH (col) AGAINST ('keyword');

The query above took from 5 - 10 seconds.
Below is MySQL (version 4.1.11) configuration:
-------------------------------------
[mysqld]

key_buffer_size = 512M
table_cache = 1024
sort_buffer_size=8M
join_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=8M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 8G
max_allowed_packet = 8M

skip-innodb
skip-bdb
ft_min_word_len=2
ft_max_word_len=31

thread_concurrency = 8
-------------------------
And the server Specification:
-------------------------------
CPU: 2 x Intel Xeon 2.8Ghz HT
Memory: 3G
Linux version: 2.6.9-1.667smp
Linux distribution: Red Hat 3.4.2-6.fc3
--------------------------------

Why the SELECT COUNT(*) on fulltext index slow? Please help.

Jul 23 '05 #1
2 3768
bo*****@gmail.com wrote:
I run a query on a 3 million rows table (avrg row length 2988) as
below:
SELECT COUNT(*) FROM tbl WHERE MATCH (col) AGAINST ('keyword');

The query above took from 5 - 10 seconds.
Below is MySQL (version 4.1.11) configuration:
-------------------------------------
[mysqld]

key_buffer_size = 512M
table_cache = 1024
sort_buffer_size=8M
join_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=8M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 8G
max_allowed_packet = 8M

skip-innodb
skip-bdb
ft_min_word_len=2
ft_max_word_len=31

thread_concurrency = 8
-------------------------
And the server Specification:
-------------------------------
CPU: 2 x Intel Xeon 2.8Ghz HT
Memory: 3G
Linux version: 2.6.9-1.667smp
Linux distribution: Red Hat 3.4.2-6.fc3
--------------------------------

Why the SELECT COUNT(*) on fulltext index slow? Please help.

Okay,

If you are searching common English text each row contains an
average of 500 words. So your query has to search an index
which contains 1.5 Billion terms. It takes a lot of disk seeks
to do a binary search on that size index.

There is a high probability that your query is disk limited.

Use some performance measurement tools and try to isolate where
the time is being spent when you are running this query.
Jul 23 '05 #2
jerry gitomer wrote:
bo*****@gmail.com wrote:
I run a query on a 3 million rows table (avrg row length 2988) as
below:
SELECT COUNT(*) FROM tbl WHERE MATCH (col) AGAINST ('keyword');

The query above took from 5 - 10 seconds.
Below is MySQL (version 4.1.11) configuration:
-------------------------------------
[mysqld]

key_buffer_size = 512M
table_cache = 1024
sort_buffer_size=8M
join_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=8M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 8G
max_allowed_packet = 8M

skip-innodb
skip-bdb
ft_min_word_len=2
ft_max_word_len=31

thread_concurrency = 8
-------------------------
And the server Specification:
-------------------------------
CPU: 2 x Intel Xeon 2.8Ghz HT
Memory: 3G
Linux version: 2.6.9-1.667smp
Linux distribution: Red Hat 3.4.2-6.fc3
--------------------------------

Why the SELECT COUNT(*) on fulltext index slow? Please help.

Okay,

If you are searching common English text each row contains an
average of 500 words. So your query has to search an index
which contains 1.5 Billion terms. It takes a lot of disk seeks
to do a binary search on that size index.

There is a high probability that your query is disk limited.

Use some performance measurement tools and try to isolate where
the time is being spent when you are running this query.


Thanks for reply.

Any recommended MySQL performance measurement tool?
If my query is disk limited, then any solution?

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Rainer | last post by:
Hi folks, Yesterday I have upgraded my linux box with a pair of Xeon 1G CPU and reinstalled everything (it was a sinle Xeon 700). The default RH9 installation comes with MySQL 3.23.54. After the...
0
by: Adam | last post by:
Hi all, a site I run has been getting busier during peak hours and I've noticed at times the mysql server will bring the entire server to a standstill. I have tweaked the mysql server...
0
by: Scott | last post by:
Hi, I'm having a problem with a new machine running Mysql version 4.0.18 on the AMD64 version of Mandrake 10.0. The new machine has got 64bit AMD processor and 2GB of RAM. Nearly all...
1
by: boonkit | last post by:
Hi all, I cannot find MySQl performance info at linux "top" command. I already tested on slow MySQL query that took more than 5 minutes (create fulltext index), but during the 5 minutes, i...
5
by: Douglas Hay | last post by:
I have a C++ Builder APP that uses the Advantage database. I want to export data from the Advantage tables to a MySQL database on my website. What is the best way to get my data into the MySQL...
1
by: PowerLifter1450 | last post by:
I've been having a very rough time installinig mySQL on Linux. I have been following the instructions form here: http://www.hostlibrary.com/installing_apache_mysql_php_on_linux Everytime I get to...
10
by: eholz1 | last post by:
Hello Members, I am setting up a photo website. I have decided to use PHP and MySQL. I can load jpeg files into the table (medium blob, or even longtext) and get the image(s) to display without...
0
by: Chrom_ | last post by:
Mysql is filling my /var partition because the log limit doesn't seem to be respected. I've tried many different settings in /etc/mysql/my.cnf but nothing works. Logrotate is not...
0
by: Pratchaya | last post by:
In my.cnf i add these lines ####### log-bin log-slow-queries = /var/log/mysqld-slow.log long_query_time=1 #######
2
by: mezise | last post by:
Posted by Pratchaya: ------------------------------------------------------ MySQL Slow Log ERROR In my.cnf i add these lines ####### log-bin log-slow-queries = /var/log/mysqld-slow.log
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.