MySQL database really slow with a really big table

Question

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.

Solution 1

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.

UPDATE:

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

RESET QUERY CACHE;

if you have reload rights, else you should use

FLUSH QUERY CACHE;

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.

So:

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;

Solution 2

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:

CREATE TABLE ii_table (....) ENGINE=MEMORY DEFAULT CHARSET=utf8

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.

Comments

[JamesRyan]

is there an index on iwebs?

[PeakGen]

@JamesRyan: Hi, Thanks for the reply, what did you mean by "index" ?

[vautee]

see [dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html] for more information about mysql and indizes. They speed up your searches (if implemented correctly)

[user9517]

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.

[PeakGen]

@JannePikkarainen: But PHPMyADMIN also took somewhat around 1 minute to show the data

[TomTom]

150 milllion rows is "really big" these days? Was smallish the last time i checked.

[PeakGen]

@TomTom: OK, but what is the issue here.?

[user9517]

BTW this isn't Stack Overflow - did you mean to post this there ?

[PeakGen]

@lain: I dnt need help with programming. Maybe with database. If I post this in StackOverflow, they will close the question telling various big reasons and say this is the place.

[TomTom]

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.

[Jenny D]

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.

[Andrea]

I don't have enough reputation to add comments.. I wanted to help.

[PeakGen]

Hi Andrea, I did the things toy asked for, please see the updated answer.

[Jenny D]

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!

[PeakGen]

Hi, so in case of index, this is the way to create it? w3schools.com/sql/sql_create_index.asp

[PeakGen]

Hi, so in case of index, this is the way to create it? w3schools.com/sql/sql_create_index.asp

[Andrea]

As I may know about your data I would create it like this: ALTER TABLE table_name ADD INDEX 'iwebs_index' ('iwebs' ASC);

[PeakGen]

I ran the command ALTER TABLE 'wordstable' ADD INDEX (iwebs). Give me few time, It is still running.

[Nebu]

See stackoverflow.com/questions/3002605/… for more information on how to create indexes on an existing table. Personally i use gui tools to alter my tables you can download the the mysql gui tools here mysql gui tools