Re: fulltext indexing and query speeds?
Hi! On Jul 29, Niels Larsen wrote: Sergei, Will a future MySQL version combine the current nice and fast word-search with ability to add data in finite time? I saw Make all operations with FULLTEXT index faster on the TODO list .. are you able to say what is in the pipe? Not really. Some changes are made in 4.1 to make searches faster - sometimes much faster. Probably these changes will make inserts will be slightly faster too. (will because it's only half-way done) But as far as inserts are concerned we don't have usable ideas yet of how to make them significantly faster. Neither it is any high in the todo :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext indexing and query speeds?
Hi! On Jul 29, Niels Larsen wrote: Sergei, I had two columns indexes of type BTREE where the .MYI file was ~5 gb. Then I decided to drop one of them and replace it with a fulltext index. The dropping took 4 hours and fulltext indexing also 4 hours. There were about You should've done it in one command: ALTER TABLE table DROP INDEX index1, ADD FULLTEXT INDEX (col1, col2); 30,000,000 rows. I found very good query speeds, only problem is large outputs, but maybe thats a temp-file thing. This gives me hope that we can use MySQL. Now I will try the other and much bigger column, which is the long-running one that I killed. The final problem (I hope) is how to add to a 30-50 gb database that has fulltext indices. I need to add 100-500 mb updates once a day; but if I just say load data infile ... similar to when I first build the database, then the loading crawls and will never finish. I havent realized a way to do this, can you think a working way? That is, I want both efficient word-search and the ability to frequently add to the database. There's little I can suggest for now :( Try to experiment with bulk_insert_buffer variable. You need both big keycache and big bulk_insert_buffer. When MERGE tables will support fulltext searches you can use them to keep small table where you insert data, and from time to time move the data from it to the big one. Actually you can do it with UNION even now, but it's not nice solution at all. Note - when you insert more than 1% of the old table size, it's faster to use ALTER TABLE t DISABLE KEYS before loading (and ... ENABLE KEYS after). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext indexing and query speeds?
Sergei, Thank you for the nice help. Try to experiment with bulk_insert_buffer variable. You need both big keycache and big bulk_insert_buffer. I lowered key_buffer_size from 512M to 256M and increased bulk_insert_buffer_size from 8M to 128M. When MERGE tables will support fulltext searches you can use them to keep small table where you insert data, and from time to time move the data from it to the big one. Actually you can do it with UNION even now, but it's not nice solution at all. Oh, I will do anything to make it work .. I will do unions between 50 selects if I must. I access MySQL via an abstracted perl api which I dont change, so I can live with awful things underneath if there is just a shiny surface. If unionizing doesnt slow fulltext search much, then I try that first. Will a future MySQL version combine the current nice and fast word-search with ability to add data in finite time? I saw Make all operations with FULLTEXT index faster on the TODO list .. are you able to say what is in the pipe? Greetings, Niels L Bioinformatics Research Center (BIRC) Aarhus University Hoegh Guldbergsgade 10 DK 8000 Aarhus C Denmark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fulltext indexing and query speeds?
Greetings, Do anyone know where to find a description of how fulltext indexing and query speeds depend on data volume? I have 30-40 gb of text distributed across 30-40 million entries, a medium size database I suppose. But I have not even been able to test a query yet, because indexing is on its second day. Which I think cant be right. I will come up with the details if anyone asks, but how long is indexing supposed to take on different amounts of data? if there is a way to predict how long it will take, then that might work; then we may simply buy a machine do nothing but indexing .. assuming the boolean mode queries finish in interactive time, ie seconds, not minutes. I use v. 4.0.12, but will happily upgrade if indexing becomes faster. Niels L Niels Larsen, Associate Professor Bioinformatics Research Center (BIRC) Aarhus University Hoegh Guldbergsgade 10 DK 8000 Aarhus C Denmark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext indexing and query speeds?
Hi! On Jul 28, Niels Larsen wrote: Greetings, Do anyone know where to find a description of how fulltext indexing and query speeds depend on data volume? I have 30-40 gb of text distributed across 30-40 million entries, a medium size database I suppose. But I have not even been able to test a query yet, because indexing is on its second day. Which I think cant be right. I will come up with the details if anyone asks, but how long is indexing supposed to take on different amounts of data? if there is a way to predict how long it will take, then that might work; then we may simply buy a machine do nothing but indexing .. assuming the boolean mode queries finish in interactive time, ie seconds, not minutes. I use v. 4.0.12, but will happily upgrade if indexing becomes faster. About indexing is on its second day... How is it done ? You insert data into the table with FULLTEXT index ? Or you add an index to existing table (with ALTER TABLE or alike) ? What does SHOW PROCESSLIST shows ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
