mysql using indexes

Posted on 2011-10-06
Last Modified: 2013-12-16
hello there,
I would like to know how can I set indexes on innodb mysql v5.5.16 running on centos v5.7

>>  MySQLTuner 1.1.1 Recommendations
Adjust your join queries to always utilize indexes

KEY BUFFER No key reads?!
Seriously look into using some indexes

Question by:XK8ER
    LVL 59

    Expert Comment

    by:Kevin Cross
    CREATE INDEX manual page should help. You can also use alter table user interface of mySQL workbench to set indexes.
    LVL 24

    Expert Comment

    Ah, so you're finally working on the indexes =).

    Generally you need to create indexes for columns that are used in the WHERE clause and in joins.  However, choose them well - indexes are most effective when there are many possible values (>50% of the number of rows).  Also, if you have many queries that filter by the same columns like select * from table1 where col1=<value> and col2=<value> and col3=<value> it can help if you create a concatenated index with all 3 columns, unless one of them is already the table's primary key.

    Where do you start?  I suggest you turn on the slow query log ( Whatever gets logged there are your slower queries which most need your attention.

    Btw,here's an old but still good tutorial on indexing:
    LVL 21

    Expert Comment

    Ah, so you're finally working on the indexes =).

    LVL 1

    Author Comment

    heheh, yes little by little im tuning this mysql server..
    I have set the slow query and its now enabled, should wait like a day before checking out the slow queries log.
    long_query_time = 4

    Open in new window

    LVL 24

    Accepted Solution

    4 seconds might be too long.  I'd go with 2 or even 1 if this is a for a web application.  Though yes, if there are queries that run for more than 4 seconds, I'd work on them first.  Maybe start with 4, but don't stop after you've tuned them.  Lower the long_query_time after that.
    LVL 59

    Assisted Solution

    by:Kevin Cross
    Additionally, you may want to log queries not using indexes:

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Using 'screen' for session sharing, The Simple Edition Step 1: user starts session with command: screen Step 2: other user (logged in with same user account) connects with command: screen -x Done. Both users are connected to the same CLI sessio…
    Join Greg Farro and Ethan Banks from Packet Pushers ( and Greg Ross from Paessler ( for a discussion about smart network …
    Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
    Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now