Re: Switching from 3.22.32 to 3.23.33. What about the database files?
Tobias - You can continue to use the ISAM format or convert to the new MYISAM format (and other formats such as HEAP, I believe). See the 'mysql_convert_table_format' script in the bin directory. -karl Tobias Wolff wrote: > > Hello, > can anyone tell me whether or not I have to convert the database files when > switching from MySQL Version 3.22.32 to Version 3.23.33. I am working on > SuSe Linux 6.4. Is it sufficient just to copy the database files? > Thanks, > Tobias. > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sometimes hanging query - more info
*
I left out a piece of probably important information the first time:
search_messages_archive contains 11.5 million records.
messages_archive contains 760,000 records.
Thanks.
-karl
*
I've been having a problem for quite awhile now with a particular query
sometimes taking a long time. The query in question is a web-based
search for messages in a bbs archive. Every time a message is added to
the bbs each word in the message is parsed out and added to the
'search_messages_archive' table. It is based on the 'Slapping together
a search engine...' article on phpbuilder.com.
When the query hangs the server load spikes and can cause other queries
to fail.
The tables involved in the query are:
mysql> show columns from search_messages_archive;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra
|
++---+--+-+-+---+
| word | char(50) | | MUL | |
|
| message_number | mediumint(8) unsigned | | MUL | 0 |
|
++---+--+-+-+---+
mysql> show columns from messages_archive;
+-+---+--+-++---+
| Field | Type | Null | Key | Default
| Extra |
+-+---+--+-++---+
| message_number | mediumint(8) unsigned | | MUL | 0
| |
| message_area_number | smallint(5) unsigned | | MUL | 0
| |
| message_area| varchar(100) | | |
| |
| topic_number| mediumint(8) unsigned | | MUL | 0
| |
| topic | varchar(100) | | |
| |
| message | text | | |
| |
| post_date | date | | | -00-00
| |
| post_time | time | | | 00:00:00
| |
| user_name | varchar(40) | | MUL |
| |
+-+---+--+-++---+
14 rows in set (0.00
sec)
Here is the query 'EXPLAINED':
EXPLAIN SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archive
WHERE messages_archive.message_number =
search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
+-+---+-++-++--+-+
| table | type | possible_keys | key
| key_len | ref
| rows | Extra |
+-+---+-++-++--+-+
| search_messages_archive | range | word,message_number | word
| 50 | NULL
| 1630 | where used; Using temporary |
| messages_archive| ref | message_number | message_number
| 3 | search_messages_archive.message_number | 19 | Using
index |
+-+---+-++-++--+-+
+
Now here are the results of the query:
>From the slow query log (26 seconds):
# Time: 010215 11:24:06
# User@Host: [nobody] @ localhost []
# Time: 26 Lock_time: 0 Rows_sent: 11
SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archive
WHERE messages_archive.message_number =
search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
The same query run from the command line: (only .39 seconds!)
mysql> SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archiveWHERE
messages_archive.message_number = search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
++
| message_number |
++
|427 |
|438 |
|622 |
| 1485 |
| 2147 |
| 1520 |
| 1675 |
|679 |
| 1021 |
| 1226 |
| 2560 |
++
11 rows in set (0.39 sec)
Even now as I run the query from the web browser again it is very fast.
I just have not been able to track down what causes this query to be s
sometimes hanging queries
I've been having a problem for quite awhile now with a particular query
sometimes taking a long time. The query in question is a web-based
search for messages in a bbs archive. Every time a message is added to
the bbs each word in the message is parsed out and added to the
'search_messages_archive' table. It is based on the 'Slapping together
a search engine...' article on phpbuilder.com.
When the query hangs the server load spikes and can cause other queries
to fail.
The tables involved in the query are:
mysql> show columns from search_messages_archive;
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra
|
++---+--+-+-+---+
| word | char(50) | | MUL | |
|
| message_number | mediumint(8) unsigned | | MUL | 0 |
|
++---+--+-+-+---+
mysql> show columns from messages_archive;
+-+---+--+-++---+
| Field | Type | Null | Key | Default
| Extra |
+-+---+--+-++---+
| message_number | mediumint(8) unsigned | | MUL | 0
| |
| message_area_number | smallint(5) unsigned | | MUL | 0
| |
| message_area| varchar(100) | | |
| |
| topic_number| mediumint(8) unsigned | | MUL | 0
| |
| topic | varchar(100) | | |
| |
| message | text | | |
| |
| post_date | date | | | -00-00
| |
| post_time | time | | | 00:00:00
| |
| user_name | varchar(40) | | MUL |
| |
+-+---+--+-++---+
14 rows in set (0.00
sec)
Here is the query 'EXPLAINED':
EXPLAIN SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archive
WHERE messages_archive.message_number =
search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
+-+---+-++-++--+-+
| table | type | possible_keys | key
| key_len | ref
| rows | Extra |
+-+---+-++-++--+-+
| search_messages_archive | range | word,message_number | word
| 50 | NULL
| 1630 | where used; Using temporary |
| messages_archive| ref | message_number | message_number
| 3 | search_messages_archive.message_number | 19 | Using
index |
+-+---+-++-++--+-+
+
Now here are the results of the query:
>From the slow query log (26 seconds):
# Time: 010215 11:24:06
# User@Host: [nobody] @ localhost []
# Time: 26 Lock_time: 0 Rows_sent: 11
SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archive
WHERE messages_archive.message_number =
search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
The same query run from the command line: (only .39 seconds!)
mysql> SELECT DISTINCT messages_archive.message_number FROM
search_messages_archive,messages_archiveWHERE
messages_archive.message_number = search_messages_archive.message_number
AND search_messages_archive.word IN ('warmoth')
LIMIT 0,11;
++
| message_number |
++
|427 |
|438 |
|622 |
| 1485 |
| 2147 |
| 1520 |
| 1675 |
|679 |
| 1021 |
| 1226 |
| 2560 |
++
11 rows in set (0.39 sec)
Even now as I run the query from the web browser again it is very fast.
I just have not been able to track down what causes this query to be so
slow at times and very fast at others.
MySQL version: 3.23.32
OS: SuSE Linux (2.2.10 kernel)
System: PIII w/ 256Mb RAM
Let me know if more info is required
TIA.
-karl
-
Before posting, please check:
http://www.mysql.com/manual.ph
