is 5 seconds for a SELECT query in a 400 MB table reasonable?

[angelcool]

I have a 400 MB table with 3,500,000 rows; It takes around 5 secs to run a simple SELECT query, does it sound a reasonable time?

 

(... so more or less 15 secs for a 2GB table )

[requinix]

How simple? How many results?

What does

EXPLAIN SELECT (the rest of your query)

produce?

[angelcool]

mysql> explain SELECT * FROM RandomNumbers WHERE random1='XPHACFYG22422DXCCH155RE682AVEEZ2' AND random2='T83WDR36JXYC1LE4U5YPM3LKUM7ZRWSB' AND random3='UKXY3QVCEJF5VJZGFNVSG842L1R4MA58';
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | RandomNumbers | ALL  | NULL          | NULL | NULL    | NULL | 5797265 | Using where | 
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+



mysql> SELECT * FROM RandomNumbers WHERE random1='XPHACFYG22422DXCCH155RE682AVEEZ2' AND random2='T83WDR36JXYC1LE4U5YPM3LKUM7ZRWSB' AND random3='UKXY3QVCEJF5VJZGFNVSG842L1R4MA58';
+---------+----------------------------------+----------------------------------+----------------------------------+
| id      | random1                          | random2                          | random3                          |
+---------+----------------------------------+----------------------------------+----------------------------------+
| 5297215 | XPHACFYG22422DXCCH155RE682AVEEZ2 | T83WDR36JXYC1LE4U5YPM3LKUM7ZRWSB | UKXY3QVCEJF5VJZGFNVSG842L1R4MA58 | 
+---------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (3.77 sec)

)

 

Table now is 685 MB.

 

 

[gizmola]

No there is something wrong.  As requinix suggest, it is probably that your query is tablescanning the entire 3.5m rows.

 

Here's a query against a table that has over a million rows in it, and as you can see it is subsecond to do this query against a varchar() column that is indexed.

 

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from ... where IP = '....';
+----------+
| count(*) |
+----------+
|     1505 | 
+----------+
1 row in set (0.01 sec)

mysql> show profiles;
+----------+------------+------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                            |
+----------+------------+------------------------------------------------------------------+
|        1 | 0.00153400 | select count(*) from ... where IP = '...' | 
+----------+------------+------------------------------------------------------------------+
1 row in set (0.00 sec)

[gizmola]

You need indexes on those columns.

[angelcool]

mysql> select count(*) from RandomNumbers;
+----------+
| count(*) |
+----------+
|  5797265 | 
+----------+
1 row in set (0.00 sec)

 

...how about that. I think is just throughput, that is multiple queries at the same moment.

[gizmola]

No, the explain plan showed exactly what we stated... it is doing a full table scan of the table. 

 

The only way to stop this from happening is to add indexes to the columns.

 

create index randomnumbers1_idx on RandomNumbers(random1);
create index randomnumbers1_idx on RandomNumbers(random1);
create index randomnumbers1_idx on RandomNumbers(random1);

 

[angelcool]

mysql> select count(*) from RandomNumbers where random1='XPHACFYG22422DXCCH155RE682AVEEZ2';
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (3.60 sec)

 

 

...mm 3.6 secs, it's  almost a 6,000,000  rows table tho. OK, I admit it, it's 32 bit CentOS running in VirtualBox; perhaps with dedicated server hardware query time will decrease.

[angelcool]

What parameter indicates the full scan you are talking about ?

[gizmola]

Explain plan please.

 

explain extended select count(*) from RandomNumbers where random1='XPHACFYG22422DXCCH155RE682AVEEZ2';

[angelcool]

mysql> explain extended select count(*) from RandomNumbers where random1='XPHACFYG22422DXCCH155RE682AVEEZ2';
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | RandomNumbers | ALL  | NULL          | NULL | NULL    | NULL | 5797265 | Using where | 
+----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.07 sec)

mysql> 

 

No, the explain plan showed exactly what we stated... it is doing a full table scan of the table.

 

What column in the explain indicates the full scan ?

[gizmola]

What was it about adding the indexes you didn't understand?  Look at the rows count..  It is equal to the number of rows in the table.  Look at the TYPE showing "ALL".  Look at the other columns showing you that no keys are bing used.  The Extra column, when using an index will show you that. 

 

Also, I don't really need any of that to tell you that searching on a column that has no index on it will tablescan.

[angelcool]

mysql> create index randomnumbers1 on RandomNumbers(random1);
ERROR 1170 (42000): BLOB/TEXT column 'random1' used in key specification without a key length

:'(

[angelcool]

...well the id in the table was indexed, so look all those timers!! DB is now 1.2GB with 10,000,000 rows!

mysql> select * from RandomNumbers where id=9800000;

+---------+----------------------------------+----------------------------------+----------------------------------+

| id      | random1                          | random2                          | random3                          |

+---------+----------------------------------+----------------------------------+----------------------------------+

| 9800000 | ED2ZBWTEGSRVTXZWF9XZ1WW3P71QR72V | AULBG5FNMW97KZTQYGGQ43IIZAZH7R48 | BF9IBEWN2WLDL6UAD1R7UZGKZ6SXNLVP |

+---------+----------------------------------+----------------------------------+----------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from RandomNumbers where id=1205254;

+---------+----------------------------------+----------------------------------+----------------------------------+

| id      | random1                          | random2                          | random3                          |

+---------+----------------------------------+----------------------------------+----------------------------------+

| 1205254 | XPHACFYG32422DXCCH155RE892AVEEZ2 | 5KR22ZUFYNUU8TRZRL6MR4SZLC14RRIL | 2ZETPYZEDKZB4H2KUXYCSH24JTY2A8D3 |

+---------+----------------------------------+----------------------------------+----------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from RandomNumbers where id=9800000;

+---------+----------------------------------+----------------------------------+----------------------------------+

| id      | random1                          | random2                          | random3                          |

+---------+----------------------------------+----------------------------------+----------------------------------+

| 9800000 | ED2ZBWTEGSRVTXZWF9XZ1WW3P71QR72V | AULBG5FNMW97KZTQYGGQ43IIZAZH7R48 | BF9IBEWN2WLDL6UAD1R7UZGKZ6SXNLVP |

+---------+----------------------------------+----------------------------------+----------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from RandomNumbers where id=8205254;

+---------+----------------------------------+----------------------------------+----------------------------------+

| id      | random1                          | random2                          | random3                          |

+---------+----------------------------------+----------------------------------+----------------------------------+

| 8205254 | B3D2SFYPYVF898RSA9AFLN1G5TYF3VF5 | PJW8PKMD7SC7RUPRTPX64PCZ8252MAW3 | JI1ZUD4RW6PDR4VBJJ7DYA3W2YPFZC8A |

+---------+----------------------------------+----------------------------------+----------------------------------+

1 row in set (0.00 sec)

 

mysql> select count(*) from RandomNumbers;         

+----------+

| count(*) |

+----------+

| 10047282 |

+----------+

1 row in set (0.00 sec)

 

mysql> explain select count(*) from RandomNumbers;

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

1 row in set (0.00 sec)

 

mysql> explain select count(*) from RandomNumbers where id=4589002;

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table        | type  | possible_keys | key    | key_len | ref  | rows | Extra      |

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

|  1 | SIMPLE      | RandomNumbers | const | PRIMARY      | PRIMARY | 8      | const |    1 | Using index |

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

1 row in set (0.09 sec)

 

mysql>

 

 

[fenway]

Huh?

[requinix]

What the heck is that table for, anyways?

[angelcool]

What the heck is that table for, anyways?

 

For testing, all it stores are random numbers. I am eventually planning to hit 10 GB (now is 1.2GB), and test/feel performance on different queries.

[requinix]

[edit] Er, misinterpreted. Nevermind.

[ignace]

@OP: execute:

SHOW CREATE TABLE RandomNumbers;

 

create index randomnumbers1_idx on RandomNumbers(random1);
create index randomnumbers1_idx on RandomNumbers(random1);
create index randomnumbers1_idx on RandomNumbers(random1);

 

Don't you mean:

 

create index randomnumbers1_idx on RandomNumbers(random1(32));
create index randomnumbers2_idx on RandomNumbers(random2(32));
create index randomnumbers3_idx on RandomNumbers(random3(32));

 

[gizmola]

We never got a structure for the table initially, but those columns should be char() or varchar().  If they are exactly 32 characters, then they should be char(32) for maximum performance.  We also don't know if the tables are myisam or innodb.