I have a MySQL table with 150 million rows. Below is its data structure.
Below is some of its data.
Now, using PHP My Admin I ran the below command.
SELECT `iwords` FROM `wordstable` WHERE `iwebs` = "a1"
It says it took less than a second to run the query. Below is the proof.
But actually, this took somewhat around 1 minute to display me the data!!
So I ran a Java code to see the time. It is below.
public void select(String str)
{
try {
long startTime = System.currentTimeMillis();
Statement s = con.createStatement();
ResultSet executeQuery = s.executeQuery("SELECT `iwords` FROM `wordstable` WHERE `iwebs` = \"a1\" ");
int r=0;
long endTime = System.currentTimeMillis();
System.out.println("Time took by Database: "+(endTime-startTime));
while(executeQuery.next())
{
r++;
}
System.out.println("Number of rows: "+String.valueOf(r));
} catch (SQLException ex) {
ex.printStackTrace();
}
}
This code printed the time as 88779 milliseconds which is 88.779 seconds!
This is a very big problem, I have an array of words to search, and if it takes 88 seconds to search a "single" word, then that would be useless!
Below are some of my high level table details.
Below is details about the server machine
So my question is, can MySQL really do this job? According to the MySQL it took less second to operate the query, but why is it taking this much of time in reallity? My future database will be bigger than this, billions of records. I need to complete this operation within 2-3 seconds atleast!
update
As requested by a SO member, I ran the below command and I am posting their results.
Input EXPLAIN SELECT iwords FROM wordstable WHERE iwebs = 'a1';
Result
Input
SET profiling=1;
SELECT iwords FROM wordstable WHERE iwebs = 'a1';
SHOW profile;
Output
Finally, I am accessing this server using Remote Desktop, but all the code and everything ran inside the server.