improve database performance

[GoogleCodeExporter]

19 Aug 2012 at 6:55:

What steps will reproduce the problem?

1. If you scan more than a million files, reporting speed is like unusable.

After evaluating how long it takes to create indexes with already a million
files inserted (only half a minute tops), I've decided to implement switching
modes. For scanning and inserting files, I will drop all indexes that can be
dropped, for reporting I will add indexes to make reporting quicker.

[GoogleCodeExporter]

19 Aug 2012 at 8:59:

So I've asked for help with my queries here:
http://stackoverflow.com/questions/12021445/mysql-query-performance-with-subsele
cts-table-with-millions-of-rows

For this purpose I've put together my queries here:
http://code.google.com/p/directory-scanner/source/browse/trunk/sql/reporingQueri
es.sql
And for the query "sql_selectSha1Collisions" I already got a good solution:

SELECT sha1
FROM files
GROUP BY sha1
HAVING COUNT(*)>1
AND MIN(size)<>MAX(size)

And in the unlikely event that this will return a non-empty result, select
details in another query with WHERE sha1 = ?

Performance: Without an index that is useable for this query, the query took
2.87 sec with 743k rows, after creating those 3 indexes:

CREATE INDEX sha1 ON files (sha1);
CREATE INDEX size ON files (size);
CREATE INDEX sha1_size ON files (sha1, size);

And running explain on the query, I found that he will only use the sha1 index.
With this, I got my answer in 1.20 sec (I've inserted one faked sha1 collision
for testing those queries)

The other question that I also wanted to have answered by my query
"sql_selectSha1Collisions" was for mistakenly multiple times inserted files
(same path, same filename)

Replacement for this is on the one hand for directories:

SELECT path
FROM directories
GROUP BY path
HAVING COUNT(*)>1

And for files this:

CREATE UNIQUE INDEX filename_dir ON files (filename, dir_id);

I cannot use a unique constraint on path, since mysql has a max key length of
767 bytes, and I've defined path as a varchar(1000)