General Community > Scripting Help

Newbie question on determining indexes in MySQL

(1/2) > >>

BigMike:
Hello,

I've been using Major Hayden's MySQLTuner for a while now and I've always seen a recommendation to "use indexes with joins" due to high joins performed without indexes. The last time I checked it was 25,441 joins performed without indexes.

I finally decided to investigate this, found an easy to read & learn article, and am now ready to apply my small knowledge on using indexes...

Question: How can I determine which queries are the ones in need of an index, or which queries are the ones that would benefit most from indexes? Is there a log somewhere that lists queries that are missing/need indexes? Or a log of the slowest queries?

I am running MySQL 5.1.63, PHP 5.3.8, and Apache 3.9.2 with cPanel/WHM atop CentOS 6.3

Thank you very much in advance!!

BigMike

vbgamer45:
General what I go by is what is in the where clause.  Such as table joins.   example table1.ID_MEMBER = table2.ID_MEMBER I would have an index on ID_MEMBER in both tables   Or if a search is happening such as search a table for an email,firstname you would want to have an index on that.
But if it LIKE with wildcards skip it since it has to scan the whole table.

If it just a boolean value of 1 or 0 skip it.

BigMike:
Hmmmmmmm

The main SQL software on my server is SMF v2.x and Drupal v6.x. (I only manage these two databases, SMF & Drupal. There are a few other db, but I assume they were installed by cPanel and I've never accessed them)

To make this even more of a newbie question, I am not the one who programmed SMF/Drupal..... so how do I know what should be improved?

I think I may just be really confused on this subject.

Knowing that the queries are created by the software developers, I trust they (SMF & Drupal) have done their best to use optimized queries... In which case, I should just continue to ignore this warning from mysqltuner.pl, right?

Sorry for the dumb questions! Thank you for helping me understand this!! :D

BigMike

BigMike:
Ah! Drupal has a SQL status report feature that I always check to compare the ratio of query cache hits and misses, and there are other information listed that I've always skipped over:

Query performance
VariableValueDescriptionSelect_full_join0The number of joins without an index; should be zero.Select_range_check0The number of joins without keys that check for key usage after each row; should be zero.Sort_scan0The number of sorts done without using an index; should be zero.Table_locks_immediate1416081The number of times a lock could be acquired immediately.Table_locks_waited648The number of times the server had to wait for a lock.
Now that I've learned a bit about joins, indexes, keys, this makes sense to me! It's been sitting right in front of me the whole time.

So I see that Drupal has ZERO joins without an index. This is great. But then where are the other twenty five thousand joins coming from? Again, is there a SQL log I can view to see what tables are being used for this?

Thanks for anyone's help!
Mike

vbgamer45:
Generally what I do to watch for slow queries. Is look at the source code but there are other ways.

If you have a slow query log in mysql you can refer to that. Or look at the sql process list and see what stuff locks or takes a while to run.

Navigation

[0] Message Index

[#] Next page