PeaTearGriffin
07-22-2008, 07:53 PM
Ok, so I've read at MySQL about how important it is to use explain. But I'm a bit confused as how to properly optimize my queries. From what I gathered:
- I should try avoid any full table scans, so whenever I see type ALL I should fix that right?
- In the extra column I should be trying to avoid using temporary and using filesort
- I should keep the rows required to be scanned to a minimum.
What I have been doing is indexing columns that I used the where clause a lot. What happens is it will definitely improve some queries, but then other queries may suffer. I also tried putting a max length on the indexes, which also seems to improve some queries, but yet again make other queries not as efficient. I also tried combining two columns as one index. Thus adding indexes have definitely helped in reducing full table scans, but I still can't get rid of using filesorts.
So I am basically creating indexes, using explain to paste the results, dropping the indexes and creating new ones and pasting those results, and repeat until I tried most combinations. I wouldn't need to do this if I actually knew what I was doing, lol! So how can you find out how to improve your query with explain?
- I should try avoid any full table scans, so whenever I see type ALL I should fix that right?
- In the extra column I should be trying to avoid using temporary and using filesort
- I should keep the rows required to be scanned to a minimum.
What I have been doing is indexing columns that I used the where clause a lot. What happens is it will definitely improve some queries, but then other queries may suffer. I also tried putting a max length on the indexes, which also seems to improve some queries, but yet again make other queries not as efficient. I also tried combining two columns as one index. Thus adding indexes have definitely helped in reducing full table scans, but I still can't get rid of using filesorts.
So I am basically creating indexes, using explain to paste the results, dropping the indexes and creating new ones and pasting those results, and repeat until I tried most combinations. I wouldn't need to do this if I actually knew what I was doing, lol! So how can you find out how to improve your query with explain?