Results 1 to 4 of 4

Hybrid View

  1. #1

    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 reportsfeature requests, and downloads at http://mysqltuner.com/
     
    >>  Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] 
    Skipped version check for MySQLTuner script
    [OKCurrently running supported MySQL version 5.0.92-community-log
    [OKOperating on 32-bit architecture with less than 2GB RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] 
    Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables112M (Tables234)
    [--] 
    Data in MEMORY tables1022K (Tables3)
    [!!] 
    Total fragmented tables17

    -------- Security Recommendations  -------------------------------------------
    [
    OKAll database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] 
    Up for: 10h 6m 3s (125K q [3.446 qps], 9K connTX3BRX45M)
    [--] 
    Reads Writes70% / 30%
    [--] 
    Total buffers106.0M global + 5.7M per thread (100 max threads)
    [!!] 
    Maximum possible memory usage674.8M (131of installed RAM)
    [
    OKSlow queries5% (7K/125K)
    [
    OKHighest usage of available connections21% (21/100)
    [
    OKKey buffer size total MyISAM indexes8.0M/54.6M
    [OKKey buffer hit rate99.8% (3M cached 6K reads)
    [
    OKQuery cache efficiency50.1% (41K cached 83K selects)
    [
    OKQuery cache prunes per day0
    [OKSorts requiring temporary tables0% (0 temp sorts 9K sorts)
    [!!] 
    Joins performed without indexes431
    [OKTemporary tables created on disk20% (726 on disk 3K total)
    [
    OKThread cache hit rate94% (539 created 9K connections)
    [
    OKTable cache hit rate77% (300 open 388 opened)
    [
    OKOpen file limit used13% (548/4K)
    [
    OKTable locks acquired immediately99% (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) 

  2. #2
    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.

  3. #3
    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    593
    Quote Originally Posted by DoHost View Post
    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.
    Correct. If the column isn't going to be used in a WHERE clause then don't index it. You will just end up wasting resources doing so.

Similar Threads

  1. Script to copy all index.htm index.html index.php from all users
    By WhiteBear in forum Hosting Security and Technology
    Replies: 2
    Last Post: 06-29-2010, 12:41 PM
  2. Setting index.html as default page instead of index.php?
    By Joel Theodore in forum Hosting Security and Technology
    Replies: 1
    Last Post: 06-23-2008, 11:53 AM
  3. static index.html pages hacked, also *index* files
    By tormeu in forum Hosting Security and Technology
    Replies: 5
    Last Post: 09-12-2007, 11:53 AM
  4. How to Index for Joins (MySQL)
    By codenode in forum Other Reviews
    Replies: 0
    Last Post: 09-24-2005, 12:46 AM
  5. Replies: 1
    Last Post: 06-16-2002, 08:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •