Results 1 to 4 of 4
Thread: How to index joins?
Hybrid View
-
04-15-2011, 03:03 AM #1Newbie
- Join Date
- Apr 2011
- Posts
- 6
How to index joins?
I'm running vbulletin 3.8. Mysqltuner says I need to utilize indexes for join queries, how do I do this?
PHP Code:>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-community-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 112M (Tables: 234)
[--] Data in MEMORY tables: 1022K (Tables: 3)
[!!] Total fragmented tables: 17
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 6m 3s (125K q [3.446 qps], 9K conn, TX: 3B, RX: 45M)
[--] Reads / Writes: 70% / 30%
[--] Total buffers: 106.0M global + 5.7M per thread (100 max threads)
[!!] Maximum possible memory usage: 674.8M (131% of installed RAM)
[OK] Slow queries: 5% (7K/125K)
[OK] Highest usage of available connections: 21% (21/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/54.6M
[OK] Key buffer hit rate: 99.8% (3M cached / 6K reads)
[OK] Query cache efficiency: 50.1% (41K cached / 83K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9K sorts)
[!!] Joins performed without indexes: 431
[OK] Temporary tables created on disk: 20% (726 on disk / 3K total)
[OK] Thread cache hit rate: 94% (539 created / 9K connections)
[OK] Table cache hit rate: 77% (300 open / 388 opened)
[OK] Open file limit used: 13% (548/4K)
[OK] Table locks acquired immediately: 99% (111K immediate / 111K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 2.0M, or always use indexes with joins)
-
04-15-2011, 03:59 PM #2WHT Addict
- Join Date
- Apr 2011
- Location
- Charlotte, NC
- Posts
- 104
When you join two tables together, you specify a field to join on.
Example
SELECT * FROM table1 LEFT JOIN table2 ON table1.foo = table2.bar;
That will display the two tables as a single result set. You should make sure that fields table1.foo and table2.bar are indexed by MySQL.
This make it so that MySQL has an easier time working with those fields. If those fields are not table indexes, then it takes longer and requires more more system resources to perform the query.
You can create indexes by:
ALTER TABLE table1 ADD INDEX (foo);
ALTER TABLE table2 ADD INDEX (bar);
this would help to reduce the time and system resources needed to perform the join query.
-
04-15-2011, 05:11 PM #3Junior Guru Wannabe
- Join Date
- Oct 2009
- Posts
- 56
Using indexes is great for SELECT queries but isn't recomended for columns that are usually updated you should take care of this point.
If you are using PhpMyAdim you can easily select the columns you want to create indexes on then click on the index icon below the table.
-
04-15-2011, 07:07 PM #4Web Hosting Master
- Join Date
- Jan 2004
- Posts
- 593
Similar Threads
-
Script to copy all index.htm index.html index.php from all users
By WhiteBear in forum Hosting Security and TechnologyReplies: 2Last Post: 06-29-2010, 12:41 PM -
Setting index.html as default page instead of index.php?
By Joel Theodore in forum Hosting Security and TechnologyReplies: 1Last Post: 06-23-2008, 11:53 AM -
static index.html pages hacked, also *index* files
By tormeu in forum Hosting Security and TechnologyReplies: 5Last Post: 09-12-2007, 11:53 AM -
How to Index for Joins (MySQL)
By codenode in forum Other ReviewsReplies: 0Last Post: 09-24-2005, 12:46 AM -
How to enable automatic display of README, HEADER, and index.txt in directory index?
By TMX in forum Hosting Security and TechnologyReplies: 1Last Post: 06-16-2002, 08:19 PM