MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Long select time in big tables
2107
October 24, 2005 01:14AM
1289
October 24, 2005 01:19AM
1206
October 24, 2005 05:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.