CODEWITHSUNDEEP
mysqlperformance
Neir0
Neir0

Reputation: 13377

Mysql slow perfomance on big table

I have following table with millions rows:

CREATE TABLE `points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DateNumber` int(10) unsigned DEFAULT NULL,
  `Count` int(10) unsigned DEFAULT NULL,
  `FPTKeyId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `index3` (`FPTKeyId`,`DateNumber`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=16755134 DEFAULT CHARSET=utf8$$

As you can see i have created indexes. I donnt know am i do it right may be not. The problem is queries execute super slow.

Let's take a simple query 

SELECT fptkeyid, count FROM points group by fptkeyid

I cannt get result because query aborting by timeout(10 min). What i am doing wrong?

Upvotes: 2

Views: 701

Answers (4)

Bradley A. Tetreault
Bradley A. Tetreault

Reputation: 464

I would personally start with your AUTO_INCREMENT value. You have set it to increase by 16,755,134 for each new record. Your field value is set to INT UNSIGNED which means that the range of values is 0 to 4,294,967,295 (or almost 4.3 billion). This means that you would have only 256 values before the field goes beyond the data type limits thereby compromising the purpose of the PRIMARY KEY INDEX.

You could changed the data type to BIGINT UNSIGNED and you would have a value range of 0 to 18,446,744,073,709,551,615 (or slightly more then 18.4 quintillion) which would allow you to have up to 1,100,960,700,983 (or slightly more then 1.1 trillion) unique values with this AUTO_INCREMENT value.

I would first ask if you really need to have your AUTO_INCREMENT value set to such a large number and if not then I would suggest changing that to 1 (or at least some lower number) as storing the field values as INT vs BIGINT will save considerable disk space within larger tables such as this. Either way, you should get a more stable PRIMARY KEY INDEX which should help improve queries.

Upvotes: 1

Luc Franken
Luc Franken

Reputation: 3014

Your query does not make sense:

SELECT fptkeyid, count FROM points group by fptkeyid

You group by fptkeyid so count is not useful here. There should be an aggregate function. Not a count field. Next that that count is also a MySQL function which makes it not very useful / advisable to use the same name for a field.

Don't you need something like:

SELECT fptkeyid, SUM(`count`) FROM points group by fptkeyid

If not please explain what result you expect from the query.

Created a database with test data, half a million records, to see if I can find something equal to your issue. This is what the explain tells me:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  points  index   NULL    index3  10  NULL    433756

And on the SUM query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  points  index   NULL    index3  10  NULL    491781

Both queries are done on a laptop (macbook air) within a second, nothing takes long. Inserting though took some time, few minutes to get half a million records. But retrieving and calculating does not.

We need more to answer your question totally complete. Maybe the configuration of the database is wrong, for example almost no memory allocated?

Upvotes: 1

biziclop
biziclop

Reputation: 14616

Beware MySQL's stupid behaviour: GROUP BYing implicitly executes ORDER BY.

To prevent this, explicitely add ORDER BY NULL, which prevents unnecessary ordering.

http://dev.mysql.com/doc/refman/5.0/en/select.html says:

If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL:

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

+

http://dev.mysql.com/doc/refman/5.6/en/group-by-optimization.html says:

The most important preconditions for using indexes for GROUP BY are that all GROUP BY columns reference attributes from the same index, and that the index stores its keys in order (for example, this is a BTREE index and not a HASH index).

Upvotes: 3

Enrico Tiongson
Enrico Tiongson

Reputation: 420

I think the problem is your server bandwidth. Having a million rows would probably need at least high megabyte bandwidths.

Upvotes: -1

Related Questions