Philipp Richter writes:
> 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?
>
>
Hi!
Please try to design a query with out LEFT [OUTER] attribute.
If you can sort tables in descending order versus N to 1 relationship,
you could try STRAIGHT join, and join without attributes.
Sinisa
+----------------------------------------------------------------------+
| TcX ____ __ _____ _____ ___ == mysql@stripped |
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic |
| /*/ /*/ /*/ \*\_ |*| |*||*| mailto:sinisa@stripped|
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+----------------------------------------------------------------------+
