Mysql: how to improve performance for this mysql select statement?

Question

I created a sql table where I indexed files on disk. On the table more than 1 million entries. I added indexes for ext and size, but it still takes more than a minute to complete this query, which tells me the amount of space used by ext. How to increase productivity with this choice?

select ext, ROUND((sum(size) / (1073741824))) as TotalSizeGB, count(*) as Count from fileindex group by ext order by TotalSizeGB desc; 

Explain the conclusion:

 || *id* || *select_type* || *table* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *Extra* || || 1 || SIMPLE || fileindex || index || _NULL_ || ext || 27 || _NULL_ || 1892234 || Using index; Using temporary; Using filesort || 

Answer 1

A recorded query will always hit every row in the table - so there really is a limit to how fast it can work. If you really want this result to be returned quickly, you can add another table to keep the total size of each ext, and update it with triggers whenever an operation is performed on your main table.

Answer 2

Use MySQL Triggers , since the INSERT'ed strings are in fileindex , it does something like UPDATE meta SET value=value+NEW.size WHERE name='fileindex.count'; .

 delimiter | DROP TRIGGER fileindexafterinsert;| CREATE TRIGGER fileindexafterinsert AFTER INSERT ON fileindex FOR EACH ROW BEGIN update meta set value=value+NEW.size where name=CONCAT('fileindex.',NEW.ext); END; | DROP TRIGGER fileindexafterdelete;| CREATE TRIGGER fileindexafterdelete AFTER DELETE ON fileindex FOR EACH ROW BEGIN update meta set value=value-OLD.size where name=CONCAT('fileindex.',OLD.ext); END; | 

Then you can just do SELECT * FROM meta WHERE name='fileindex.exe' LIMIT 1 , which should return in less than 0.01 seconds.

Answer 3

Since I don't see an obvious flaw in the MySQL syntax, if you want it faster, I would suggest going NoSQL and using a document database that supports Map-Reduce , like Hadoop or CouchDB . You can place this on a cluster (count: hundreds) of machines on EC2 (ok, I'm joking, but seriously, you can run 1 node for the processor core for maximum speed on 1 field).

Answer 4

Your query will fall into each table entry, so you do not want to slow down by first clicking on the index and then the table, as this will obviously lead to 2 IOs per record from the table (1 for the index and 1 for the actual table data).

So the first question is, how can you speed up scanning a complete table?

Set up IO. Fast, defragmented, unpartitioned disks (with other data, applications, etc.) etc.

Rather, consider denormalization; for example, a trigger on your table that counts and sums the corresponding data with each insert, update, and delete and stores that value in another table. Then a query for one row of data in this other table.

Answer 5

Add a coverage index that will basically have all the columns you need in memory. I would recommend: alter table fileindex add index covering (ext,TotalSizeGB, size)

Should work well. (Will hope)