Hi, We are developing an application that needs to use a massive back-end database. The database will contain around 75 million rows with around 80 columns per row. We would prefer to use MySQL as the database platform as it is free. The MySQL database would be hosted on a dedicated server that we will purchase from a web hosting company.
This database would be used both by our customers and by our own employees. The first column will contain some text which will be unique in each row. 90% of the remaining columns will containing numbers and the other columns will contain text. The second column will contain numbers and it needs to be updated on a monthly basis. But, we also need to store historical data regarding the value of the second column for each row for the last 24 months, on a rolling basis. This can either be done by adding more columns to the same table, or by putting this historical data in a separate table, depending on your recommendations. Users will make 2 types of queries on this database: i) The first type of query is what can be called a mission-critical query - these queries will be made by our customers and the results of these queries must be returned within 30 seconds at the most; otherwise, customers are not going to want to use the application. This query would basically involve asking the customer for a search string, searching the FIRST column (and ONLY the first column) of the entire database to find out each row that contains that search string (either in whole or in part) and then returning all such rows to the user sorted in descending order of the SECOND column. Only the information in the first 2 columns will be returned to the customers - the information in the other 78 columns will not be returned to the customers. Customers will also have the option of specifying negative matches - i.e. if the first column of a particular row contains any one of a list of banned words or phrases, then that row will not be returned even if it contained the primary search string. ii) The second type of queries are non-mission-critical; these would be run by our employees and it is ok if these queries take as much as 10 minutes to return results. However, the queries that our employees will run are also much more complex - they will specify multiple search criteria - for instance, "return all rows for which the 60th column has a value > 2000 and the minimum value for the columns 40, 41, ... 50 for that row is 20 and the 35th column of that row is < 5" etc. It is quite possible that as many as 20 - 30 users will be querying the database at the same time. Furthermore, there will be 5 - 6 different PHP scripts that are going to constantly update the different columns and rows of the database with the values. Here are my questions: i) Is MySQL a realistic option for this kind of database? ii) What should be the hardware configuration (processor type, number of processors, RAM etc.) for the dedicated server that will host this database. We are interested in the most cost-effective option; i.e. the least powerful hardware configuration that is suitable for running this system. iii) What is the best way of designing the database architecture in order to ensure that we are able to meet our targets regarding the query times? I might need to ask some follow-up questions based on your initial response. Please be extremely detailed and specific in answering the questions, especially the third question. For the third question, please suggest the table structure including whether we should keep all the data in a single table or whether we should use multiple tables. If we use multiple tables, how many such tables should we use, and which columns should be present in each table? Also, we need to know what should be the primary key, the unique key etc. for each table and how the indexes should be defined. Alternatively, if you recommend that we break up the database into multiple tables not in terms of columns but in terms of rows (i.e. keeping a fixed number of rows per table), then what is the ideal number of rows that should be present in each table? And, depending on your recommendation regarding the database structure, please advise us regarding how we should build the queries for the 2 types of queries that we need to support. I would prefer that someone who has actual experience designing a MySQL database with similar or even more demanding characteristics and requirements answer my query. We are looking for the right persion who can come up with a consultancy. Remunarations will not be any constraint for some body who can provide the right solution. If you need any clarifications from us before you can answer the question, feel free to ask. Suryya
