Hi!
I'm developing a Photo-Database system using MySQL 3.22.22 and PHP 3.0.8
(the latest PHP version segfaults on my Alpha developing machine).
(BTW: the application will be released under GPL when it is ready...)
I'm having some difficulties using indexes to speed up selects. MySQL
doesn't use the indexes I have created.
The query I would like to speed up with indexes is:
This query returns all pictures from a specified folder (6 in this case)
SELECT p.id, p.number, p.title, p.takenon, p.format, p.status,
f.title, f.name, ph.name, fi.name, ca.name,
(pa.folder_id=6) AS is_alias
FROM picture p, folder f
LEFT JOIN photographer ph ON p.takenby=ph.id
LEFT JOIN camera ca ON p.camera=ca.id
LEFT JOIN film fi ON p.film=fi.id
LEFT JOIN picture_alias pa ON pa.pic_id=p.id
WHERE p.folder=f.id AND (p.folder=6 OR pa.folder_id=6)
GROUP BY p.id
ORDER BY p.timestamp
The 'explain' from the above query is:
+-------+------+---------+--------+---------+------+------+-------------+
| table | type |possible | key | key_len | ref | rows | Extra |
+-------+------+---------+--------+---------+------+------+-------------+
| p | ALL | NULL | NULL | NULL | NULL | 1328 | |
| ph | ALL | PRIMARY | NULL | NULL | NULL | 0 | |
| f | ALL | PRIMARY | NULL | NULL | NULL | 38 |range checked|
| |for each |
| |record (index|
| |map: 1) |
| ca | ALL | PRIMARY | NULL | NULL | NULL | 0 | |
| fi | ALL | PRIMARY | NULL | NULL | NULL | 0 | |
| pa | ref | pic_id | pic_id | 4 | p.id | 1 | where used; |
| | Using index |
+-------+------+---------+--------+---------+------+------+-------------+
This is without indexes. I have tried various combinations but without
luck. I didn't get MySQL to use an index for 'picture'. So my question is:
***
*** Which indexes do I have to create so that mysql uses them on the
*** above query??????????????
***
The 'description's from the tables:
pictures are stored in the filesystem. each picture has 3 sizes
(thumbnail,normal,big). the pictures are numbered incrementaly for each
folder.
mysql> describe picture;
+-------------+------------------+------+---+------------+--------------+
| Field | Type | Null |Key| Default | Extra |
+-------------+------------------+------+---+------------+--------------+
| id | int(10) unsigned | |PRI| 0 |auto_increment|
| timestamp | timestamp(14) | YES | | NULL | |
| folder | int(10) unsigned | |MUL| 0 | |
| number | int(10) unsigned | | | 0 | |
| takenby | int(10) unsigned | | | 0 | |
| title | varchar(100) | | | | |
| takenon | date | | | 0000-00-00 | |
| format | varchar(10) | | | | |
| film | int(10) unsigned | | | 0 | |
| camera | int(10) unsigned | | | 0 | |
| status | enum('new','enabled','disabled','protected') ... |
| description | text | YES | | NULL | |
+-------------+------------------+------+---+------------+--------------+
each picture is located in a folder. for now no subfolders are supported.
name is the filename of the folder. descpic is a picture id. this picture
will be displayed on the folder-choose page.
mysql> describe folder;
+-------------+------------------+------+-----+--------+----------------+
| Field | Type | Null | Key | Default| Extra |
+-------------+------------------+------+-----+--------+----------------+
| id | int(10) unsigned | | PRI | 0 | auto_increment |
| timestamp | timestamp(14) | YES | | NULL | |
| name | varchar(255) | | MUL | | |
| title | varchar(255) | | | | |
| status | enum('enabled','disabled','protected') ... |
| descpic | int(10) unsigned | | | 0 | |
| description | text | YES | | NULL | |
+-------------+------------------+------+-----+--------+----------------+
each picture has a photographer :)
mysql> describe photographer;
+-------------+------------------+------+-----+--------+----------------+
| Field | Type | Null | Key | Default| Extra |
+-------------+------------------+------+-----+--------+----------------+
| id | int(10) unsigned | | PRI | 0 | auto_increment |
| timestamp | timestamp(14) | YES | | NULL | |
| name | varchar(255) | | | | |
| email | varchar(255) | | | | |
| http | varchar(255) | | | | |
| description | text | YES | | NULL | |
+-------------+------------------+------+-----+--------+----------------+
a camera can be associated with each picture
mysql> describe camera;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | 0 | auto_increment |
| name | varchar(50) | | | | |
+-------+------------------+------+-----+---------+----------------+
a film can be associated with each picture
mysql> describe film;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | 0 | auto_increment |
| name | varchar(50) | | | | |
| push | mediumint(9) | | | 0 | |
| cros | enum('y','n') | | | y | |
+-------+------------------+------+-----+---------+----------------+
picture alias is used for pictures which appear in multiple folders
mysql> describe picture_alias;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| pic_id | int(10) unsigned | | PRI | 0 | |
| folder_id | int(10) unsigned | | PRI | 0 | |
+-----------+------------------+------+-----+---------+-------+
thank you very much and greetings from vienna ;)
btw: thanx also to the MySQL-Team for the EXCELLENT database!
--
ciao Philipp
---------------[ Linux now!!! ]----------------
Linux - Why use Windows, since there is a door?
