I am planning to have a database of size more than 12 million records all of them in a single table and no other joins etc, used for search, filtered based on field names of the table, approximately 4 GB size in MYSQL backend and php frontend.
Question is can MYSQL support databases with sizes around 4 GB, Will I have any performance issue when I do a search using php
Will I be able to get results in atleast 30 to a 60 Secs
your suggestions please
Appropriate indexes and simple queries will handle SELECT performance issues.
Yes, you will have performance issues. Every application has database performance issues, even with much smaller databases than yours. You need to study and work to understand how to use the RDBMS technology to its best advantage.
The good news is that you can do it. A database of 4GB is not excessive, and many success stories involve databases that are much larger.
I suggest you start by studying the following resources:
Combining technology like the APC cache or Memcached with PHP and MySQL is also practically required for high-performance PHP applications. Because the fastest database query is the one you don't have to run (because the data is already in a cache).
We can't answer your question about getting query results in 30 to 60 seconds, because we don't know what type of queries you need to run. There are many other factors for performance. The naive and general nature of your questions indicates that you have a lot of study to do before you will be successful.
Just as a point of reference, we have a 15GB database, but it's split among various tables. The largest tables have the simplest structures, that helps keep performance up.
There is no fundamental reason why a 4GB database with 12 million rows should have performance issues, but thats not to say it wont. If you are doing basic select queries on a single table with reasonable indexes your results should return in a matter of seconds not 20 - 30 seconds. Loading the result set (if it is large) into your page might take a considerable lenght of time.
Why not try it out? Can you load your data into a test server running mysql and do a few test queries, that would give you a good indication to begin with.
Take a look at these links:
http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
http://www.allinterview.com/showanswers/35641.html
MySQL works with two known DB engines, MyISAM and InnoDB. InnoDB is known for its high performance on large scale databases.
Yes, of course mysql can support small databases such as 4G. You won't have performance issues, as your database server will have > 4G of ram, so the entire database can be kept in ram.
If you had wanted to use a large database, MySQL can of course support that as well, but you won't be able to keep say, 1Tb, in ram.