Long select time in big tables
Posted by:
Karolis
Date: October 24, 2005 01:14AM
hello,
I use mysql as a database for images. My problem is: I want to select details from image table by ImageID (primary key). This is a very simple select, however it takes more time than I could expect.
SELECT statement:
SELECT ImageID, Width1, Height1, FileName
FROM IMAGES
WHERE ImageID IN(102116,144855,188030,243406,247262,297552,309613,310700,426370,446944);
Having in mind that ImageID is a primary key, I would expect to get results very quickly, however, this query takes about 0.2 to 0.4 seconds to return.
My server: Dual xeon 2.4 with 3GB ram and 4SCSI disks with 2 raid1 setups.
I had an idea that if I kept required data in an index, it would work faster, however, the table is too big to add indexes to it. Trying to add additional indexes to this table would take forever and crash mysql eventually. The size of the table is about 40GB.
Here are the details about the table:
mysql> describe IMAGES;
+--------------+--------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------------+------+-----+-------------------+----------------+
| ImageID | int(11) | | PRI | NULL | auto_increment |
| PostID | int(11) | YES | MUL | NULL | |
| AltTitle | varchar(100) | YES | | NULL | |
| FileName | varchar(50) | | | | |
| ContentType | enum('gif','jpeg','png') | | | jpeg | |
| Content1 | mediumblob | | | | |
| SizeBytes1 | int(11) | | | 0 | |
| Width1 | int(11) | | | 0 | |
| Height1 | int(11) | | | 0 | |
| Content2 | mediumblob | YES | | NULL | |
| SizeBytes2 | int(11) | YES | | NULL | |
| Width2 | int(11) | YES | | NULL | |
| Height2 | int(11) | YES | | NULL | |
| Content3 | mediumblob | YES | | NULL | |
| SizeBytes3 | int(11) | YES | | NULL | |
| Width3 | int(11) | YES | | NULL | |
| Height3 | int(11) | YES | | NULL | |
| ContentOrg | mediumblob | | | | |
| SizeBytesOrg | int(11) | | | 0 | |
| WidthOrg | int(11) | | | 0 | |
| HeightOrg | int(11) | | | 0 | |
| Modified | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------------+--------------------------+------+-----+-------------------+----------------+
Here are the indexes:
mysql> show index from IMAGES;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| IMAGES | 0 | PRIMARY | 1 | ImageID | A | 396759 | NULL | NULL | | BTREE | |
| IMAGES | 1 | IMG_Post_Image | 1 | PostID | A | 198379 | NULL | NULL | YES | BTREE | |
| IMAGES | 1 | IMG_Post_Image | 2 | ImageID | A | 396759 | NULL | NULL | | BTREE | |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
If anyone knows how to select faster (I expect no more than 0.01 sec), please let me know :)
thanks,
Karolis