List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:August 9 1999 7:05pm
Subject:Re: Index Problems ...
View as plain text  
Philipp Richter wrote:
> 
> 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       |              |
> +-------------+------------------+------+---+------------+--------------+
< cut > 

Hi Philipp

For this query you need a KEY on picture over takenby, camera, film, id and folder.
But the WHERE ... OR ... part will break it.

As you GROUP and ORDER the results, you should try to use a temporary table and two insert
statements and the one SELECT... GROUP BY...ORDER BY ...

Tschau
Christian

Thread
Index Problems ...Philipp Richter7 Aug
  • Index Problems ...sinisa7 Aug
    • Re: Index Problems ...Philipp Richter7 Aug
      • Re: Index Problems ...sinisa7 Aug
  • Index Problems ...Michael Widenius8 Aug
  • Re: Index Problems ...Christian Mack9 Aug