Re: mysqldump running out out of memory
Hi, Yes I'm using the -q option with mysqldump, but the result is the same. This is a replicated environment and the master is running FreeBSD and the slave NetBSD and on the master which only has InnoDB tables there is no problems to run a dump but the machine is to loaded so we can not afford to run the dump there. The tables on the slave is mostly Myisam, maybe there is some kind of memory buffer that I'm missing to tune on NetBSD but i can't figure out what it can be, I've already increased the ulimit values for the session running the dump. // Fredrik Atle Veka wrote: Have you tried this flag? -q, --quick Don't buffer query, dump directly to stdout. On Sun, 29 Apr 2007, Fredrik Carlsson wrote: Hi, I have a problem with mysqldump, its exiting with the message mysqldump: Error 5: Out of memory (Needed 22042208 bytes) when dumping table `theTable` at row: 2990911 I have searched the archives and tried what people suggested but nothing seems to work, I'm dumping using the following command: /usr/pkg/bin/mysqldump -h localhost -B theDatabase --skip-opt --max_allowed_packet=1024M -q Any tips on how to get the dump running? the dump should be about 15-20GB in size the fully dumped, but I never seems to get there. // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump running out out of memory
Hi, I have a problem with mysqldump, its exiting with the message mysqldump: Error 5: Out of memory (Needed 22042208 bytes) when dumping table `theTable` at row: 2990911 I have searched the archives and tried what people suggested but nothing seems to work, I'm dumping using the following command: /usr/pkg/bin/mysqldump -h localhost -B theDatabase --skip-opt --max_allowed_packet=1024M -q Any tips on how to get the dump running? the dump should be about 15-20GB in size the fully dumped, but I never seems to get there. // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance
Hi, I'm running FreeBSD 6.1, mysql 5.0.24a(binaries from mysql ab) and are running some benchmarks using sysbench, what kind of performance should i expect? The machine is a Dell poweredge 1800 with the cerc raid controller (two sata disks in raid1). sysbench command: sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=500 --num-threads=16 --mysql-socket=/tmp/mysql.sock --mysql-user=abc--mysql-password=def --oltp-test-mode=complex --max-requests=1 run On a single disk (not using the cerc controller) I get the following results: OLTP test statistics: queries performed: read:14 write: 5 other: 2 total: 21 transactions:1 (413.70 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 19 (7860.35 per sec.) other operations:2 (827.41 per sec.) Test execution summary: total time: 24.1719s total number of events: 1 total time taken by event execution: 384.3010 per-request statistics: min:0.0061s avg:0.0384s max:0.3780s approx. 95 percentile: 0.0752s Threads fairness: events (avg/stddev): 625./16.48 execution time (avg/stddev): 24.0188/0.01 I guess the disk cache is causing the many transactions/s? On a raid1 using the cerc hw raid controller: OLTP test statistics: queries performed: read:140140 write: 50023 other: 20010 total: 210173 transactions:1 (53.48 per sec.) deadlocks: 10 (0.05 per sec.) read/write requests: 190163 (1016.93 per sec.) other operations:20010 (107.01 per sec.) Test execution summary: total time: 186.9973s total number of events: 1 total time taken by event execution: 2988.9251 per-request statistics: min:0.0087s avg:0.2989s max:186.9913s approx. 95 percentile: 0.0499s There a quite big difference between the single disk and the raid1, should i expect more or is this normal? // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about innodb, ibdata1
Hi, I'm using InnoDB with tablespaces for almost all tables. The last few week the file ibdata1 has started to grow, should it really do this when using tablespaces? i did an alter on one of the bigger tables some time ago to alter the size of a varchar collumn, could this has something to do with it? I'm using MySQL 4.1.11 // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
Hi, My original query was a UNION query :) and that one is really fast. The problem i had was that every 8-12 day mysql sad that all of my max_connections was in use. I think i will stick with my UNION query it seems faster. // Fredrik. Bill Easton wrote: Fredrik, I haven't read all of the earlier messages, but it looks like your problem is that a query such as the following takes more than a minute to return 11 rows. Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685; Why? Well, your explain says that no key is used, but the entire table is scanned (type='ALL'). In particular, MySQL is reading every record of A and looking to see if either A.id=560685 or B.parent=560685. Not good. Your query is equivalent to the following: Select id from art where id=560685 or parent=560685; I'd be surprised if the simpler query weren't slightly faster than the original--MySQL has more work to do on the original. It appears that, with the more complex query, you are trying to fool MySQL into using indexes for both parts of the OR. It didn't work. In an earlier mail, your explain had type='index' and key='id_3'. In that case, you evidently had an index, id_3, that contained both id and parent. So, MySQL could get all of the information form the id_3 index; therefore, it read the index instead of the table. It still read the entire index, looking to see if either A.id=560685 or B.parent=560685. Better than reading the full table, but still not good. What to do? Well, you can get the information you want in TWO queries: Select id from art where id=560685; Select id from art where parent=560685; In each of these, MySQL will surely use the appropriate index and return the result in a few milliseconds. You should be able to combine them into one query and get the same behavior: Select id from art where id=560685 UNION ALL Select id from art where parent=560685; I'd be surprised if MySQL didn't do fine on this. (You may have tried this and failed--as I said, I didn't try to read all of the earlier mail. But I'd be astonished if it weren't fast, though I suppose MySQL might try to do something fancy, knowing that the two queries are on the same table. In any event, check the two individual queries. If necessary, use a temporary table.) Then, you get to add your ORDER BY clause; presumably, it will still be fast. There was some mention in earlier mail of joins being faster than unions. That may be, but the difference should be too small to notice, unless different indexes are used. In your query above, with the inner join, MySQL is going to first consider the join and then consider the use of indexes for the where clause--so it ends up with the full table scan. HTH, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
mysql describe art; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | parent | int(11) | YES | MUL | NULL|| | bodyid | int(11) | YES | | NULL|| | lst| varchar(80) | YES | MUL | NULL|| | mf | varchar(80) | YES | | NULL|| | mt | varchar(80) | YES | | NULL|| | subc | varchar(200) | YES | MUL | NULL|| | sdate | varchar(45) | YES | | NULL|| | batch | varchar(80) | YES | MUL | NULL|| | mgid | varchar(90) | YES | | NULL|| | date| datetime | YES | MUL | NULL|| +-+--+--+-+-++ 11 rows in set (0.12 sec) mysql show index from art; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | id_2 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_2 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 141 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1177 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A | 67501 | NULL | NULL | YES | BTREE | | | art | 1 | id_3 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_3 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | id_3 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | parent |1 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | subc |1 | subc | A | 40501 | NULL | NULL | YES | FULLTEXT | | +++--+--+-+---+-+--++--++-+ 19 rows in set (0.04 sec) The query: Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id) order by A.date; Would return several hundered thousend answers so i added a where option, i dont know if this was what you had in mind. The way i used your query it should be the same thing as: Select A.id, A.parent from art A where A.id=60 or A.parent=60 order by A.date; mysql explain Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or A.parent=60 order by A.date;
Re: Max connections being used every 10-12 day.
I really appreciate your help :) I did some cleanup of my indexes(there are a couple of them left to clean out but it takes so long time): mysql show index from art; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 542437 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 216 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 183 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1802 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 216 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | parent |1 | parent | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | mid|1 | mid | A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | date_2 |1 | date| A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | subc |1 | subc | A | 54243 | NULL | NULL | YES | FULLTEXT | | | art | 1 | mf|1 | mf | A | 54243 | NULL | NULL | YES | FULLTEXT | | +++--+--+-+---+-+--++--++-+ 13 rows in set (0.00 sec) mysql Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685 order by A.date; +++ | id | parent | +++ | 560685 | 0 | | 560707 | 560685 | | 560714 | 560685 | | 560780 | 560685 | | 560783 | 560685 | | 560802 | 560685 | | 560810 | 560685 | | 560851 | 560685 | | 560855 | 560685 | | 561056 | 560685 | | 561104 | 560685 | +++ 11 rows in set (1 min 12.45 sec) mysql explain Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685 order by A.date; +---+++-+-+---+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+---+++ | A | ALL| PRIMARY| NULL|NULL | NULL | 542437 | Using filesort | | B | eq_ref | PRIMARY,parent | PRIMARY | 4 | mail_archive.A.id | 1 | Using where| +---+++-+-+---+++ 2 rows in set (0.00 sec) // Fredrik Donny Simonton wrote: Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
It is a single PIII 500MHz, so i just changed thread_concurrency to 2 :), thanks The slow query log don't show that many slow queries, but they did show alot of queries that was'nt using any index, can these queries cause some kind of occasional lock up? Is there a faster way to perform this query? (SELECT id,parent FROM art WHERE id=495098 ORDER BY date) UNION (SELECT id,parent FROM art WHERE parent=495098 ORDER BY date); explain show the following ++---+--+-+-+---+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | ++---+--+-+-+---+--+-+ | art | const | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | const |1 | | | art | ref | parent | parent | 5 | const |2 | Using where; Using filesort | ++---+--+-+-+---+--+-+ // Fredrik Carlsson Donny Simonton wrote: What kind of box is this? According to you're my.cnf it looks like it's a either a dual with hyperthreading or a quad box. I don't see that you have your slow query log turned on, this should be the first thing you should do in my opinion. This is what mine looks like. ### Slow Query Information ### log-long-format log-slow-queries log-queries-not-using-indexes set-variable= long_query_time=3 Then go in and fix all of those that are showing up in the slow query log. With 4-5 queries per second, you should NEVER fill up the 200 connections unless you just have some awful queries or you have some tables that are getting corrupted and are being repaired during that time. Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
The inner join statement returned the same stuff but it was not as fast as the union is and the inner join seems to use more cpu resources. Could these union queries really be the problem behind my occasional lock ups and that 200 connections being used? i mean the server is not that loaded and the http logs show amazeingly low http traffic the night of the lock up. I upgraded mysql to 4.0.22 and tuned down thread_concurency to 2 to se if that helps. When these lock ups occures it dont seems like mysql is freeing the connections, for exampel the last time it happend was around 02:00 a couple of days ago and when i checked the server 14 hours later (16:00) it still said that max_connections was full and mysqld was idling using 0% of the CPU, if the queries was queued up would'nt mysql at least show some activity? // Fredrik Donny Simonton wrote: Frederick, What exactly are you trying to accomplish? Personally, I don't recommend using union unless absolutely necessary, since most people don't really understand when it should be used. And I think it shouldn't be used in this case either. Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id) order by A.date; See if that gives you the same results as the original query and then explain it to see if you get anything differently. Also what is the table structure including indexes of the table? Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max connections being used every 10-12 day.
Hi list, I have a rather serious problem that i really dont know how to solve, Every 8-12 day my mysql server stops responding and i get the error code that indicates that max_connections are full, the problem is that i have checked all of my code over and over again to se that every connections are closed properly and they are. No persistent connections are being used and the max connections error allways occures at night 02:00-03:00, my httpd logs shows no unnormal amount of traffic at the time. The last time this happend i tuned the wait_timeout down to 15 seconds to se if that helped, but no effect :( The server is running NetBSD 1.6.2 and mysql 4.0.21 I really need help on this one because i dont know what is causing max_connections to be used all at once or how to reproduce the error, i only know that it happens very periodicly and 'show full processlist' hardly ever shows any connections not even the day/hours before the error. The server has about 4-5 queries / seconds. According to the manual the max_connections have one connection reserved for the superuser but i have never been able to use that extra connection to se which user that is eating upp all the connections. // Fredrik Carlsson # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock skip-locking key_buffer = 280M max_allowed_packet = 32M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections = 200 wait_timeout = 15 connect_timeout = 5 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
mysql has about 50GB of temp space to work with so thats not the problem. Its strange because the server is not that loaded and around 12-04 at night no cronjobs that affects mysql or general server performance are being run. Is is possible to se how many queued up questions mysql has at the moment? Peter Lovatt wrote: Hi there a are a couple of things that I have found cause occasional lock ups. running out of temp space - MySql builds temp files on bigger queries and if it runs out of temp disk space it grinds to a halt, which causes all the following queries to queue up until max_connections is exceeded. the second cause is one massive query - big tables, lots of joins etc - that takes so much processing power that there is nothing left. MySql and perhaps the server slows down to the point that it cannot process any more queries, and again the queue builds until max_connections is exceeded. if the lockup occurs overnight this might be the time the server is doing housekeeping - apache log analysis for example - so it may be the server is loaded too heavily to manage MySql queries too, so again the queue builds up. Just a few thoughts - hope it helps. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
converting to Innodb.
Hi list, I have a question regarding mysql and innodb. My current setup uses myisam and the db size is about 1.6 GB with two table that each have about 500k rows. I perform alot of fulltext search on these tables and they can sometimes take along time to finish and when the table is locked during the fulltext search everything else stops working. I read at dev.mysql.com that myisam uses per table lock and innodb per row lock so my question is if i convert my tables to innodb will i be able to use the tables during a fulltext search? // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting to Innodb.
Roger Baklund wrote: Fredrik Carlsson wrote: Hi list, I have a question regarding mysql and innodb. My current setup uses myisam and the db size is about 1.6 GB with two table that each have about 500k rows. I perform alot of fulltext search on these tables and they can sometimes take along time to finish and when the table is locked during the fulltext search everything else stops working. I read at dev.mysql.com that myisam uses per table lock and innodb per row lock so my question is if i convert my tables to innodb will i be able to use the tables during a fulltext search? According to the manual: FULLTEXT indexes are used with MyISAM tables only URL: http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html URL: http://dev.mysql.com/doc/mysql/en/Fulltext_Restrictions.html Sorry, i forgot that fulltext only works for myisam ;) i guess that a move to innodb is not that interesting anymore. // Fredrik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld segfaults.
Hi, I have problem with my mysqld, when its receiving alot of connections and at the same time doing insert/delete/update/select on the same table mysqld segfaults and restarts 041108 0:59:08 Warning: Got signal 14 from thread 162602 Segmentation fault 041708 13:17:54 -e \nNumber of processes running now: 0 041708 13:17:54 mysqld restarted 041108 13:17:55 InnoDB: Started /usr/pkg/libexec/mysqld: ready for connections. Version: '4.0.21' socket: '/tmp/mysql.sock' port: 3306 Source distribution 041108 13:19:06 Warning: Got signal 14 from thread 2 I start mysqld with these flags: --key_buffer_size=130M --table_cache=256 --sort_buffer_size=16M --read_buffer_size=4M --query_cache_size=64M ulimit -n 8096 ulimit -l 80 ulimit -d 80 ulimit -m 80 ulimit -p 9000 ulimit -s 60 The table has about 450k rows and the size with indexes is ~1.4GB Running NetBSD 1.6.2 and mysql 4.0.21 What can cause these segfaults? and is there anything i can tune to get rid of them? // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHECK TABLE looks up all databases.
Hi, I have some questions regarding CHECK TABLE, OPTIMIZE TABLE. When i run these commands on table1 mysql seems to lock up all other databases, is it possible to run these things in the background so that other databases can be accessible. // Fredrik Carlsson. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext performance problem.
Hi all,
I'm running a small mail archive and have a little problem with the
fulltext search performance.
I really appreciate any tips/design suggestions (even if it dont have to
do with the search problem ;) ).
Database schema:
mysql describe msg_header;
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| id | int(11) | | PRI | NULL| auto_increment |
| parent | int(11) | YES | MUL | NULL||
| bodyid | int(11) | YES | | NULL||
| list| varchar(80) | YES | MUL | NULL||
| mfrom | varchar(80) | YES | | NULL||
| mto | varchar(80) | YES | | NULL||
| subject | varchar(200) | YES | MUL | NULL||
| mcc | varchar(80) | YES | | NULL||
| sdate | varchar(45) | YES | | NULL||
| batch | varchar(80) | YES | MUL | NULL||
| msgid | varchar(90) | YES | | NULL||
| date| datetime | YES | MUL | NULL||
+-+--+--+-+-++
12 rows in set (0.00 sec)
mysql describe msg_body;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| id| int(11) | | PRI | NULL| auto_increment |
| body | text| YES | MUL | NULL||
+---+-+--+-+-++
2 rows in set (0.00 sec)
index from msg_body;
*** 1. row ***
Table: msg_body
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*** 2. row ***
Table: msg_body
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*** 3. row ***
Table: msg_body
Non_unique: 1
Key_name: body
Seq_in_index: 1
Column_name: body
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
3 rows in set (0.00 sec)
The search querys using fulltext indexes takes around 1minute and no
one want to use a search that slow :/
The Query is the following:
SELECT msg_header.bodyid,msg_header.id,
msg_header.subject,msg_header.mfrom, msg_header.date, msg_header.list FROM
msg_header,msg_body WHERE msg_header.bodyid=msg_body.id AND
match(msg_header.list) against('LISTNAME')
AND match(msg_body.body) AGAINST('WORD');
For a couple of month ago the msg-body and msg-headers parts where in
the same table and the fulltext search was really fast 1 sec, but
everything else just became slower so i splitted it upp in two tables.
But now i need to match msg_header.bodyid against msg_body.id to be able
to now witch body that belongs to with header and i think thats where
things get slow..
I ran an explain select query and the following result turned upp
++--+-+-+-+---+--+-+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
++--+-+-+-+---+--+-+
| msg_header | fulltext | list_4 | list_4 | 0
| |1 | Using where |
| msg_body | eq_ref | PRIMARY,id,body | PRIMARY | 4 |
msg_header.bodyid |1 | Using where |
++--+-+-+-+---+--+-+
Does this means that the fulltext index on msg_body.body is'nt being used?
The machine is an Intel PIII 500Mhz, 512MB memory and IDE disks running
NetBSD 1.6.3, mysql 4.0.20.
The database contains about 300 000 rows and the size is ~1GB.
// Fredrik Carlsson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fetch data and search on different tables.
Hi,
thanks for your answer.
I tried what you suggested:
select table1.id,table1.name from table1,table2 where
table1.textid=table2.id AND match(table2.text)
AGAINST('$searchString' IN BOOLEAN MODE);
But that query takes for ever to run and uses 100% cpu. Is there a
faster way to do this query?
// Fredrik Carlsson
Egor Egorov [EMAIL PROTECTED] wrote:
Something like:
SELECT ... FROM one, two WHERE textid=two.id AND MATCH(text) AGAINST() ..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fetch data and search on different tables.
This query was pretty fast avg. 1-3 s / search
SELECT m1.id FROM table1 AS t1 JOIN table AS t2 ON m1.textid=m2.id WHERE
match(t2.text) AGAINST('$searchString' IN BOOLEAN MODE);
I also added an index on table1.textid and table2.id.
Is there a way to optimize this query even more?
// Fredrik Carlsson
Fredrik Carlsson [EMAIL PROTECTED] wrote:
Hi,
I have a question regarding to search with fulltext on table and fetch
the data from another.
Table one:
id, textid, name, number, url
Table two:
id, text
On table two there is a fulltext index.
These two tables recently was one table, but i had to split them due to
the amount of data.
textid in table one is refering to id in table two, in order to keep
person and text together.
Before the split my search question looked like this:
select id,name,number,url match(text)
against('$searchString') as relevance from table where
match(text) against('$searchString' IN BOOLEAN MODE)
having relevance 0.9 order by relevance DESC
But due to the split of the table i cant really figure out how to make
the question.
I want to search with fulltext on table2(text) and fetch all the
corresponding data from table1 where textid=(table2.id).
Any tips?
Egor Egorov [EMAIL PROTECTED] wrote:
Something like:
SELECT ... FROM one, two WHERE textid=two.id AND MATCH(text) AGAINST() ..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fetch data and search on different tables.
Hi,
I have a question regarding to search with fulltext on table and fetch
the data from another.
Table one:
id, textid, name, number, url
Table two:
id, text
On table two there is a fulltext index.
These two tables recently was one table, but i had to split them due to
the amount of data.
textid in table one is refering to id in table two, in order to keep
person and text together.
Before the split my search question looked like this:
select id,name,number,url match(text)
against('$searchString') as relevance from table where
match(text) against('$searchString' IN BOOLEAN MODE)
having relevance 0.9 order by relevance DESC
But due to the split of the table i cant really figure out how to make
the question.
I want to search with fulltext on table2(text) and fetch all the
corresponding data from table1 where textid=(table2.id).
Any tips?
Best regards
// Fredrik Carlsson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
