Question about indexes

[dognobbler]

I am new to using indexes and need a little help. I want to create a index to help the following statement run faster, can anyone suggest which would be the best index to create

select * from profile, album, movie, book, contact
where profile.album = album.albumid
and profile.movie = movie.movieid
and profile.book = book.bookid
and profile.username=contact.username
and profile.sex != "male"
and profile.searchsex != "female"
and profile.relationship != "dating"
and photo_validated != "yes";


Also is there a way to see which indexes yo have already created ie. is there a "show indexes" or "desc indexes" statement that will show me all the indexes on a database or table.

Thanks

Andy

 

[TonyGroves]

You could start by redoing your joins as follows:
[tt]
SELECT *
FROM
profile
JOIN album
ON
profile.sex <> "male"
AND profile.searchsex <> "female"
AND profile.relationship <> "dating"
AND profile.photo_validated <> "yes"
AND profile.album = album.albumid
JOIN movie ON profile.movie = movie.movieid
JOIN book ON profile.book = book.bookid
JOIN contact ON profile.username=contact.username
[/tt]
Then you could add indexes on profile.album, profile.movie, profile.book, profile.username, album.album_id, movie.movie_id, book.book_id, contact.username.

If profile.sex and profile.searchsex only have two values with a roughly-equal distribution, then an index would provide little or no benefit. Profile.relationship and profile.photo_validated might also fall into this category.

As regards your table design, you would probably be better-off converting profile.sex, profile.searchsex, profile.relationship, and photo_validated to CHAR(1) or TINYINT fields, since that would use less space (and therefore be faster) than storing multi-character strings.

[tt]SHOW INDEX FROM tablename[/tt] will list all the indexes for a table.

-----
ALTER world DROP injustice, ADD peace;