> JM> Primary keys aren't any faster than normal keys. They're simply for > JM> identification. (correct me if I'm wrong, that is my understanding) > JM> If you don't already have an index on created, I'd do that. > > I do - here's a question though, what is the best way to have an > index? To combine multiple fields into one index - or to have one > field per index? I ask because MySQL will appear to select the best > possible index for the query and sometimes it's wrong - I read > somewhere you can control which index it uses, but if you had a > "combined" index would this make things any more/less effective? Well, it can't use a multi-column index if you are only matching on one column. If you have 3 indexes, (status, boardid), (status), and (boardid), it won't slow you down any on your queries. If one index is needed it is put into memory and stored there until another index bumps it, otherwise it resides on disk. I don't see it has too much of a performance dock, it may slow down inserts a little... I am not sure about this, but I think if you are going to have the 2 column indexes, they have to match up in both tables you're joining. You'll have to experiment with it a bit. you can say "select * from thread (index statusBoardCombinedIndex) where" to get the index you want. > > JM> If you want to optimize things further, I would run the query at midnight > JM> each morning, and instead of paging through the entire table, just page > JM> through that temporary table. > > Figured someone might suggest that - I will have to look into it. > Thanks. > > -- > Best regards, > Richard Davey > http://www.phpcommunity.org/wiki/296.html > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
