MySQL Database - Million Entries - Performance

Question

I have a Database with ~ 15 Million Entries.

Here is its structure:

SELECT count(*) FROM hash.list; ~ 77 seconds
SELECT * FROM hash.list where plain = "aaaa"; ~~ 94 seconds

How can i make this faster ~ 5 seconds or what else?

How is this question related to PHP and md5? I would also recommend you to post this question in http://dba.stackexchange.com/ instead. — M. Eriksson, Oct 05 '16 at 06:19
Use indexes! With an index on `plain` (e.g. `alter table hash.list add index (plain(30))`, phpmyadmin doesn't do it for you for `text`-type), your 2nd query should need just milliseconds. This will not improve `count(*)`. 77s seems a lot for 15 million rows with an int primary key, it should be more in the neighborhood of 2-4 seconds, and indicates a read performance from your hdd of ~0,8mb/s. Maybe you are doing a lot of other things on the hdd - or other users in e.g. shared hosting. You can only improve this by improving your overall system performance (especially a faster hdd or more ram). — Solarflare, Oct 05 '16 at 08:47

score 2 · Answer 1 · answered Oct 05 '16 at 06:31

2

You can do following things to make it fast 1. Idexing ,I think you have done 2. Make partition of this table and only fetch records which fall under your criteria . 3. Also choos Explain to get what is taking most of time in your query .

answered Oct 05 '16 at 06:31

Mukesh Swami

415
2
11

`PARTITION` won't help; use an index. – Rick James Oct 05 '16 at 15:26

score 1 · Accepted Answer · edited May 23 '17 at 11:48

Some suggestions:

Add indexes. First add a clustered index: https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html and then add secondary indexes on the things you need to search on, like your plain column.
Buy more memory. It is obvious that your count(*) query is disc based, since 1.5G/20MB/sec ~= 75 seconds.
Use estimates and shortcuts if you can. E.g. examples here on how to get count(*) for a whole table very fast: How to optimize COUNT(*) performance on InnoDB by using index

MySQL Database - Million Entries - Performance

2 Answers2