Performance of MySQL table

Question

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.

Neville Kuyt · Accepted Answer · 2012-03-29 09:06:00Z

2

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.

answered Mar 29, 2012 at 9:06

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

symcbean Over a year ago

True, but if it were me I'd go further and move the blobs into a separate table with an auto-increment PK, and index the base table more extensively.

el smu Over a year ago

That exactly solved my problem. I just removed the primary key in the time row and now, it takes less than 100 ms to load data -> thanks a lot! Whats the advantage of putting the blobs into a separate table?

Collectives™ on Stack Overflow

Performance of MySQL table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related