Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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));

        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.

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!

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

Input

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

Output

enter image description here

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

share|improve this question

closed as off-topic by Iain, Jenny D, HopelessN00b, Andrew, TomTom Apr 24 at 12:50

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions must demonstrate a minimal understanding of the problem being solved. Try including attempted solutions, why they didn't work, and the expected results. See How can I ask better questions on Server Fault? for further guidance." – Iain, Jenny D, HopelessN00b
If this question can be reworded to fit the rules in the help center, please edit the question.

2  
is there an index on iwebs? –  JamesRyan Apr 24 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. –  Iain Apr 24 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. –  Janne Pikkarainen Apr 24 at 11:26
3  
150 milllion rows is "really big" these days? Was smallish the last time i checked. –  TomTom Apr 24 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 at 12:50

2 Answers 2

up vote 3 down vote accepted

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;
share|improve this answer
    
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 at 11:31
    
I don't have enough reputation to add comments.. I wanted to help. –  Andrea Apr 24 at 11:33
    
Hi Andrea, I did the things toy asked for, please see the updated answer. –  GloryOfSuccess Apr 24 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 at 11:35
    
Hi, so in case of index, this is the way to create it? w3schools.com/sql/sql_create_index.asp –  GloryOfSuccess Apr 24 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:

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.

share|improve this answer
    
Hi, so in case of index, this is the way to create it? w3schools.com/sql/sql_create_index.asp –  GloryOfSuccess Apr 24 at 11:45
    
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 –  Nebu Apr 24 at 12:42

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