MYSQL Indexes

capbiker

Can anybody recommend any good tutorials on indexes for mysqls?

Here is the type of querys that I am running on my website. It's currently being developed so I am the only one who can access it on my computer. But the querys are basic, and I want to the get them as optimised as I can before my website go live so not to course too many problems in the furture.

SELECT n.*, m.username FROM fss_mod_notes n, fig_user m WHERE n.mod_site_id = 14 AND n.mod_user_id = m.userid or n.mod_site_id = 16 AND n.mod_user_id = m.userid or n.mod_site_id = 19 AND n.mod_user_id = m.userid or n.mod_site_id = 17 AND n.mod_user_id = m.userid

cap

Sxooter

Here's the super simple index tutorial.

Indexes speed up reads. The more selective your reads, the more the index will generally speed things up. Indexes slow down writes. The more indexes you have to update, the slower your writes will be.

Indexes are generally desired on columns that will be used in join clauses.

Indexes are desired / often required for things like primary keys or unique constraints.

explain <your query here> will tell you how well your query is using indexes. If you aren't sure you need one, you can run explain, add the query, and run explain again to get a good idea.

DoctorWho

Index the fields that you are searching on.

What's with with the Site ID's??? I would look at trying to have the n.mod_site_id = 14, 16 etc etc. Something seems odd their but I don't know what exactly you are trying to accomplish with that.

This link can help.
http://www.databasejournal.com/features/mysql/article.php/1382791

There are also numerous parameters based on your hardware that you can fiddle with as well.

I have tables with over 5 Million records on 3Ghz Window NT Machines with 1 Gig of Ram.

Ram made the biggest difference in speed.

but learn how to optimize your queries and all the AND and OR with the mod.Site.ID stuff just seems odd to me. But it may be working just fine for you, so be it.

csn

n.mod_site_id in (1,2,3)...

capbiker

Yep, that is something else I am going to be doing soon is optimizing all my querys before my website goes live.

I am going to be needing to read up on that too soon, as I can do basicly querys like that. But I do know that it can be greatly improved on.

Reading through that website and Sxooter's post. And I can understand that you would want to have indexes on tables that get viewed more then being writtien to and that you would get some loss because of the extra information that needs to be written to it. But if it's greatly improves the access time then it would be acceptable in the long run in my view.

As I think it's better to have indexes and having your querys optimized while you website is still pretty small. And then doing it when it grows so big that there is speed loss because you ain't using them.

But something that I don't understand yet is how the join actually works. I have seen it being used before. But I cannot understand why you have got left and right joins for?

cap