>>>>> "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
