List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 8 1999 1:19pm
Subject:Index Problems ...
View as plain text  
>>>>> "Philipp" == Philipp Richter <philipp@stripped> writes:

Philipp> Hi!
Philipp> I'm developing a Photo-Database system using MySQL 3.22.22 and PHP 3.0.8
Philipp> (the latest PHP version segfaults on my Alpha developing machine).
Philipp> (BTW: the application will be released under GPL when it is ready...)

Philipp> I'm having some difficulties using indexes to speed up selects. MySQL
Philipp> doesn't use the indexes I have created.

Philipp> The query I would like to speed up with indexes is:
Philipp> This query returns all pictures from a specified folder (6 in this case)

Philipp> SELECT    p.id, p.number, p.title, p.takenon, p.format, p.status,
Philipp>           f.title, f.name, ph.name, fi.name, ca.name,
Philipp>           (pa.folder_id=6) AS is_alias
Philipp> FROM      picture p, folder f
Philipp> LEFT JOIN photographer ph ON p.takenby=ph.id
Philipp> LEFT JOIN camera ca ON p.camera=ca.id
Philipp> LEFT JOIN film fi ON p.film=fi.id
Philipp> LEFT JOIN picture_alias pa ON pa.pic_id=p.id
Philipp> WHERE     p.folder=f.id AND (p.folder=6 OR pa.folder_id=6)
Philipp> GROUP BY  p.id
Philipp> ORDER BY  p.timestamp

Philipp> The 'explain' from the above query is:

Philipp> +-------+------+---------+--------+---------+------+------+-------------+
Philipp> | table | type |possible | key    | key_len | ref  | rows | Extra       |
Philipp> +-------+------+---------+--------+---------+------+------+-------------+
Philipp> | p     | ALL  | NULL    | NULL   |    NULL | NULL | 1328 |             |
Philipp> | ph    | ALL  | PRIMARY | NULL   |    NULL | NULL |    0 |             |
Philipp> | f     | ALL  | PRIMARY | NULL   |    NULL | NULL |   38 |range checked|
Philipp> |                                                         |for each     | 
Philipp> |                                                         |record (index|
Philipp> |                                                         |map: 1)      |
Philipp> | ca    | ALL  | PRIMARY | NULL   |    NULL | NULL |    0 |             |
Philipp> | fi    | ALL  | PRIMARY | NULL   |    NULL | NULL |    0 |             |
Philipp> | pa    | ref  | pic_id  | pic_id |       4 | p.id |    1 | where used; |
Philipp> |                                                         | Using index |
Philipp> +-------+------+---------+--------+---------+------+------+-------------+

Philipp> This is without indexes. I have tried various combinations but without
Philipp> luck. I didn't get MySQL to use an index for 'picture'. So my question is:

Hi!

How big are your tables?  According to the 'rows' column above most
tables are empty (or nearly empty).  In this case MySQL uses table
scanning as this is faster than using indexes!

Fix:

Add more data to your tables!

Regards,
Monty

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