I have a MySQL table with 150 million rows. Below is its data structure.

enter image description here

Below is some of its data.

enter image description here

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.

enter image description here

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));
        System.out.println("Number of rows: "+String.valueOf(r));
    } catch (SQLException ex) {

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.

enter image description here

Below is details about the server machine

enter image description here

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!


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 enter image description here


SET profiling=1;
SELECT iwords FROM wordstable WHERE iwebs = 'a1';
SHOW profile;


enter image description here

Finally, I am accessing this server using Remote Desktop, but all the code and everything ran inside the server.

  • 2
    is there an index on iwebs?
    – JamesRyan
    Apr 24 '14 at 11:19
  • 1
    I don't think that you need help with system administration at the moment (although I guess you will in the future) - you really need help on database design, programming and general basic concepts. Right now you're so out of your depth.
    – user9517
    Apr 24 '14 at 11:26
  • 1
    Perhaps your PHPMyAdmin actually did fetch only 400 rows (or, at least parsed only 400 rows and did stop then), whereas your Java application does fetch all 132 million rows. Apr 24 '14 at 11:26
  • 3
    150 milllion rows is "really big" these days? Was smallish the last time i checked.
    – TomTom
    Apr 24 '14 at 11:30
  • 1
    Ok, this either is a compically small server with a totally not enough RAM and IO subsystem - OR it is a programming question (missing index etc.) Still, I think dba.stackexchange.com is the better place.
    – TomTom
    Apr 24 '14 at 12:50

Do you have any indexes on that table?

Please post the output of:

EXPLAIN SELECT iwords FROM wordstable WHERE iwebs = 'a1';

Then post the output of the following set of commands:

SET profiling=1;
SELECT iwords FROM wordstable WHERE iwebs = 'a1';
SHOW profile;

This will tell you where MySQL hangs and then you will at least know where to begin.


After watching your updated question, I don't know why MySQL can't find a suitable key to run your query. How vast is the set of values that 'iwebs' can contain? Your durations are clearly taken from cache. You should reset MySQL query cache via


if you have reload rights, else you should use


If you can't do this command too you will have to restart your MySQL server instance.

Re-run your commands with SQL_NO_CACHE flag, as Nebu said, just to be sure it doesn't get in the way.


EXPLAIN SELECT SQL_NO_CACHE iwords FROM wordstable WHERE iwebs = 'a1';
SET profiling=1;
SELECT SQL_NO_CACHE iwords FROM wordstable WHERE iwebs = 'a1';
SHOW profile;
  • While this is useful, it's not really an answer to the question. Requests for more info should be a comment, not posted as an answer.
    – Jenny D
    Apr 24 '14 at 11:31
  • I don't have enough reputation to add comments.. I wanted to help.
    – Andrea
    Apr 24 '14 at 11:33
  • Hi Andrea, I did the things toy asked for, please see the updated answer.
    – PeakGen
    Apr 24 '14 at 11:34
  • 1
    I understand that, I just wanted to explain why there's a risk that your answer will be deleted, since it's not really an answer... it's clear that you know what you're talking about, however, so you should have no problem getting enough rep to comment soon!
    – Jenny D
    Apr 24 '14 at 11:35
  • Hi, so in case of index, this is the way to create it? w3schools.com/sql/sql_create_index.asp
    – PeakGen
    Apr 24 '14 at 11:45

In think the query is cached. Try

SELECT SQL_NO_CACHE iwords FROM wordstable WHERE iwebs = 'a1';

This will give a better indication of how long it takes to executed the query. Also make sure iwebs is indexed. And last to really speed up your queries store the table in memory:


Storing the table in memory does have some implications. For example every time the mysql server is stopped the table information is gone. Personally for these kinds of situations i create two tables. One memory table in which the queries are performed and one disk table. When mysql starts it loads the disk table into memory.


Not the answer you're looking for? Browse other questions tagged or ask your own question.