CODEWITHSUNDEEP
mysqlperformance
el smu
el smu

Reputation: 35

Performance of MySQL table

I have a table created like this: 

CREATE TABLE rh857_omf.picture(MeasNr TINYINT UNSIGNED, ExperimentNr TINYINT UNSIGNED,
Time INT, SequenceNr SMALLINT UNSIGNED, Picture MEDIUMBLOB, PRIMARY KEY(MeasNr, 
ExperimentNr, Time, SequenceNr));

The first four rows MeasNR, ExperimentNr, Time and SequenceNr are the identifiers and are set as primary key. The fifth row, Picture, is the payload. Its an 800x800 Pixel 8Bit grey value picture (Size = 625 kBytes).

If I want to load a picture, I use the following command:

SELECT Picture FROM rhunkn1_omf.picture WHERE MeasNr = 2 AND 
ExperimentNr = 3 AND SequenceNr = 150;

In the MySQL workbench, I see the duration and the fetch time if I run this command! For smaller tables (800 MBytes, 2376 entries, picture 640x480), its very fast (<100ms). If I take a bigger table (5800 MBytesm, 9024 entries), it gets very slow (>9s).

For instance, I run the following command (on the big table):

SELECT Picture FROM rhunkn1_omf.picture WHERE MeasNr = 2 AND 
ExperimentNr = 3 AND SequenceNr = 1025 LIMIT 0, 1000;

the first time it takes 5.2 / 3.9 seconds (duration / fetch). The same command for the second time takes 0.2 / 0.2 seconds. If I change the SequenceNr

SELECT Picture FROM rhunkn1_omf.picture WHERE MeasNr = 2 AND 
ExperimentNr = 3 AND SequenceNr = 977 LIMIT 0, 1000;

its also very fast 0.1 / 0.3 seconds

But if I change the the ExperimentNr, for instance

SELECT Picture FROM rhunkn1_omf.picture WHERE MeasNr = 2 
AND ExperimentNr = 4 AND SequenceNr = 1025 LIMIT 0, 1000;

it takes long time 4.4 / 5.9 seconds.

Does anybody know why the database behaves like that and how I could improve the speed? Does it help if I create several smaller picture tables and split the load for each table? By the way, I use MySQL 5.1.62 and the MyISAM tables, but I also tested InnoDB which was even slower.

Upvotes: 1

Views: 736

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29619

It would help if you could post the EXPLAIN for the query - mostly, the answers are in there (somewhere).

However, at a guess, I'd explain this behaviour by the fact your primary key includes TIME, and your queries don't; therefore, they may make only partial use of the index. I'd guess the query plan uses the index to filter out records in the MEASNR and ExperimentNr range, and then scans for matching sequenceNrs. If there are many records which match the first two criteria, that could be quite slow.

The reason you see a speed up second time round is that the queries get cached; this is not hugely predictable, depending on load, cache size etc.

Try creating an index which matches your "where" clause, and see EXPLAIN tells you.

Upvotes: 2

Related Questions