I am going to migrate an MS Access database from a desktop computer with about half a million records to a cloud Mysql, for the users edit in frontend with wpdatatables. I am aware of features like server side processing, which will be mandatory in this case. But the questions are about the best strategy, the most efficient and safe way to keep this database:
1) Should I create the tables inside the wordpress database or create a separate mysql connection? I would like to hear about performance and safety issues of one and other option and how these will affect the performance of the wordpress site itself (slow down the site).
2) If the best performance or safety is achieved with a separate mysql connection, would be better to host this database (a) in the same server of wordpress; (b) in a separate server of the same host; (c) another host (like AWS, for example)? How those options will affect query performance and security? In other words, which one will give the best balance of performance/security?
3) Finally, shall I import the tables directly to wpdatatables, using csv or excel for example or import first to mysql, using phpmyadmin, and then generate a query to MySQL? How will these affect insert, update and query performance?
I hope you can advise me based in your experience.
At the end of last week we didn't work because of the national holiday.We are located in Serbia and our working time is from 10:00 to 17:00 CET. business days.
You can use all scenarios. Loading large data from database depends of the server configuration(php,mysql version, Apache configuration...) Our plugin sanitize all unpredictable data that can harm your application or database. My suggestion is to make table in mysql database(importing data from csv or importing sql file from other database) and then use it in our plugin by query SELECT * FROM table_name. Please avoid using database like a prefix in query(SELECT * FROM database_name.table_name).
Also if you will use some complicated queries probably wpDataTable will not work correctly with server-side processing, this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (happens rarely, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also server-side processing feature adds this part of statement automatically when users triggers the sorting on the front-end, and having it in initial statement may cause the table to crash.
Hello,
I am going to migrate an MS Access database from a desktop computer with about half a million records to a cloud Mysql, for the users edit in frontend with wpdatatables. I am aware of features like server side processing, which will be mandatory in this case. But the questions are about the best strategy, the most efficient and safe way to keep this database:
1) Should I create the tables inside the wordpress database or create a separate mysql connection? I would like to hear about performance and safety issues of one and other option and how these will affect the performance of the wordpress site itself (slow down the site).
2) If the best performance or safety is achieved with a separate mysql connection, would be better to host this database (a) in the same server of wordpress; (b) in a separate server of the same host; (c) another host (like AWS, for example)? How those options will affect query performance and security? In other words, which one will give the best balance of performance/security?
3) Finally, shall I import the tables directly to wpdatatables, using csv or excel for example or import first to mysql, using phpmyadmin, and then generate a query to MySQL? How will these affect insert, update and query performance?
I hope you can advise me based in your experience.
Hi Luis,
Thank you for your purchase.
Sorry for late response.
At the end of last week we didn't work because of the national holiday.We are located in Serbia and our working time is from 10:00 to 17:00 CET. business days.
You can use all scenarios. Loading large data from database depends of the server configuration(php,mysql version, Apache configuration...) Our plugin sanitize all unpredictable data that can harm your application or database. My suggestion is to make table in mysql database(importing data from csv or importing sql file from other database) and then use it in our plugin by query SELECT * FROM table_name. Please avoid using database like a prefix in query(SELECT * FROM database_name.table_name).
Also if you will use some complicated queries probably wpDataTable will not work correctly with server-side processing, this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (happens rarely, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Kind Regards,
Isidora Markovic
wpDataTables: FAQ | Facebook | Twitter | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Amelia demo sites | Docs
You can try our wpDataTables add-ons before purchase on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables