Hi all, I'm using the latest RMySQL to send requests to a MySQL database (which is quite small for the time - let's say 20 tables with a total of 1000 rows), and this should be part of a whole decisionnal system. It produces results which are displayed on a website, and therefore I need fast results (the database being quite small, I think this must be possible !). Unfortunately, a simple request to the database, and R needs more than 10 seconds. Even dbListTables() takes a long while. Imagine the problem with more data ! Can I hope something better with RMySQL ? Did anyone have the same problem ? If RMySQL (or is it MySQL's fault ?) is so slow, I could replace MySQL with Postgres, but this would imply a lot of extra work. R is perhaps very slow anyway ? Any piece of advice ? Thanks. Laurent
What exactly are you timing? For me running a query is instant. Are you starting R for each query, for example? What OS and what hardware are you using? People have reported that RODBC is a lot faster than RMySQL for some tasks involving large datasets. On Mon, 5 May 2003, Laurent Faisnel wrote:> I'm using the latest RMySQL to send requests to a MySQL database (which > is quite small for the time - let's say 20 tables with a total of 1000 > rows), and this should be part of a whole decisionnal system. It > produces results which are displayed on a website, and therefore I need > fast results (the database being quite small, I think this must be > possible !). > Unfortunately, a simple request to the database, and R needs more than > 10 seconds. Even dbListTables() takes a long while. Imagine the problem > with more data ! Can I hope something better with RMySQL ? Did anyone > have the same problem ? > If RMySQL (or is it MySQL's fault ?) is so slow, I could replace MySQL > with Postgres, but this would imply a lot of extra work. R is perhaps > very slow anyway ? Any piece of advice ?-- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595
On Mon, 2003-05-05 at 09:56, Laurent Faisnel wrote:> Hi all, > I'm using the latest RMySQL to send requests to a MySQL database (which > is quite small for the time - let's say 20 tables with a total of 1000 > rows), and this should be part of a whole decisionnal system. It > produces results which are displayed on a website, and therefore I need > fast results (the database being quite small, I think this must be > possible !). > Unfortunately, a simple request to the database, and R needs more than > 10 seconds. Even dbListTables() takes a long while. Imagine the problem > with more data ! Can I hope something better with RMySQL ? Did anyone > have the same problem ? > If RMySQL (or is it MySQL's fault ?) is so slow, I could replace MySQL > with Postgres, but this would imply a lot of extra work. R is perhaps > very slow anyway ? Any piece of advice ? > Thanks. > Laurent > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://www.stat.math.ethz.ch/mailman/listinfo/r-helpHi Have you tried to run the sql statement in mysql to check if the problem is really in RMySQL ? MySQL databases can be very slow if you don't index the tables. Check http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#MySQL_indexes Regards EJ
On Mon, 2003-05-05 at 11:03, Laurent Faisnel wrote:> Ernesto Jardim wrote: > > >On Mon, 2003-05-05 at 09:56, Laurent Faisnel wrote: > > > > > >>Hi all, > >>I'm using the latest RMySQL to send requests to a MySQL database (which > >>is quite small for the time - let's say 20 tables with a total of 1000 > >>rows), and this should be part of a whole decisionnal system. It > >>produces results which are displayed on a website, and therefore I need > >>fast results (the database being quite small, I think this must be > >>possible !). > >>Unfortunately, a simple request to the database, and R needs more than > >>10 seconds. Even dbListTables() takes a long while. Imagine the problem > >>with more data ! Can I hope something better with RMySQL ? Did anyone > >>have the same problem ? > >>If RMySQL (or is it MySQL's fault ?) is so slow, I could replace MySQL > >>with Postgres, but this would imply a lot of extra work. R is perhaps > >>very slow anyway ? Any piece of advice ? > >>Thanks. > >>Laurent > >> > >>______________________________________________ > >>R-help at stat.math.ethz.ch mailing list > >>https://www.stat.math.ethz.ch/mailman/listinfo/r-help > >> > >> > > > >Hi > > > >Have you tried to run the sql statement in mysql to check if the problem > >is really in RMySQL ? MySQL databases can be very slow if you don't > >index the tables. > > > >Check > >http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#MySQL_indexes > > > >Regards > > > >EJ > > > > > > > > > I'll try at once. My tables are not indexed. But I thought this could > not explain everything. Anyway, a few tests will be profitable. Thanks >I've had some surprises with table indexing ... The documentation says it can be 100 times faster! Regards EJ