Hi Guys, I'm having the same problem with SQL_CALC_FOUND_ROWS Any query involving SQL_CALC_FOUND_ROWS takes ages!! All my tables are normalized, indexed, and optimized and the query uses the indices correctly. Many people responded to my previous thread (when I though it was a fulltext issue only) and suggested multipling many of the buffers by 3 or 4 times the my-huge.cnf configuration... haven't seen much improvement. - See Variables listed below -
It just seems that its very easy to choke this server when SQL_CALC_FOUND_ROWS or
COUNT(*) is used. The number of rows is needed for pagination and to show the user
the number of matches for a given category.
- See example below for a sample of the performance issues -
SETUP:
Running MySQL 4.0.20-standard-log
Official MySQL RPM
On Dual Xeon 2GHz with 2GB RAM, no slaves, no RAID
No other users connected.
VARIABLES:
SHOW VARIABLES;
+---------------------------------+------------------+
| Variable_name | Value
|
+---------------------------------+------------------+
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 8388608
|
| concurrent_insert | ON
|
| connect_timeout | 5
|
| convert_character_set |
|
| datadir | /var/lib/mysql/
|
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| flush | OFF
|
| flush_time | 0
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_min_word_len | 3
|
| ft_max_word_len | 254
|
| ft_max_word_len_for_sort | 20
|
| ft_stopword_file | (built-in)
|
| have_bdb | NO
|
| have_crypt | YES
|
| have_innodb | YES
|
| have_isam | YES
|
| have_raid | NO
|
| have_symlink | YES
|
| have_openssl | NO
|
| have_query_cache | YES
|
| ... innodb stuff...
|
| interactive_timeout | 28800
|
| join_buffer_size | 258048
|
| key_buffer_size | 943718400
|
|... stuff ...
|
| max_allowed_packet | 1047552
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connections | 100
|
| max_connect_errors | 10
|
| max_delayed_threads | 20
|
| max_insert_delayed_threads | 20
|
| max_heap_table_size | 67107840
|
| max_join_size | 4294967295
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_sort_length | 1024
|
| max_user_connections | 0
|
| max_tmp_tables | 32
|
| max_write_lock_count | 4294967295
|
| myisam_max_extra_sort_file_size | 268435456
|
| myisam_max_sort_file_size | 2147483647
|
| myisam_repair_threads | 1
|
| myisam_recover_options | OFF
|
| myisam_sort_buffer_size | 268435456
|
| net_buffer_length | 16384
|... stuff ...
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 2097152
|
| query_cache_size | 20971520
|
| query_cache_type | ON
|
| query_prealloc_size | 8192
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 16773120
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| rpl_recovery_rank | 0
|
| server_id | 1
|
| slave_net_timeout | 3600
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /var/lib/mysql/mysql.sock
|
| sort_buffer_size | 62914552
|
| sql_mode | 0
|
| table_cache | 1024
|
| table_type | MYISAM
|
| thread_cache_size | 32
|
| thread_stack | 126976
|
|
| tmp_table_size | 239075328
|
| tmpdir | /tmp/
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| version | 4.0.20-standard-log
|
| version_comment | Official MySQL RPM
|
| version_compile_os | pc-linux
|
| wait_timeout | 28800
|
EXAMPLE:
- Here's an example using a FULLTEXT index just for simplicity since it only uses 1
table. However, I do have a few other queries where the SQL_CALC_FOUND_ROWS bogs it
down consideribly.
mysql> SELECT product_id FROM product_fulltext WHERE MATCH(search_text)
AGAINST('Blue') LIMIT 20;
+------------+
| product_id |
+------------+
| 3567602 |
| 1819473 |
| 3567549 |
| 3567449 |
| 3567609 |
| 3567529 |
| 3567604 |
| 3567607 |
| 3567606 |
| 3567408 |
| 3567605 |
| 3567603 |
| 3567600 |
| 3567601 |
| 3576159 |
| 3571258 |
| 3567625 |
| 3571547 |
| 3571265 |
| 3463427 |
+------------+
20 rows in set (0.36 sec)
mysql> SELECT SQL_CALC_FOUND_ROWS product_id FROM product_fulltext WHERE
MATCH(search_text) AGAINST('Blue') LIMIT 20;
+------------+
| product_id |
+------------+
| 3567602 |
| ... etc ..
| 3463427 |
+------------+
20 rows in set (1 min 4.84 sec)
mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS product_id FROM product_fulltext WHERE
MATCH(search_text) AGAINST('Blue') LIMIT 20;
+------------------+----------+---------------+-------------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+------------------+----------+---------------+-------------+---------+------+------+-------------+
| product_fulltext | fulltext | search_text | search_text | 0 | | 1 |
Using where |
+------------------+----------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.02 sec)
mysql> DESCRIBE product_fulltext;
+-------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+-------+
| product_id | int(9) | | PRI | 0 | |
| search_text | text | | MUL | | |
+-------------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SHOW INDEX FROM product_fulltext;
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| product_fulltext | 0 | PRIMARY | 1 | product_id | A
| 3237981 | NULL | NULL | | BTREE | |
| product_fulltext | 1 | search_text | 1 | search_text | A
| 3237981 | NULL | NULL | | FULLTEXT | |
+------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
Thanks for any advice... I'm not much of a hardware or server guy.
- John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
