[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • Last Modified:

mysql using indexes

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

>> MYSQL PERFORMANCE TUNING PRIMER
KEY BUFFER No key reads?!
Seriously look into using some indexes

0
XK8ER
Asked:
XK8ER
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
http://dev.mysql.com/doc/refman/5.5/en/create-index.html
CREATE INDEX manual page should help. You can also use alter table user interface of mySQL workbench to set indexes.
0
 
johanntagleCommented:
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 (http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html). Whatever gets logged there are your slower queries which most need your attention.

Btw,here's an old but still good tutorial on indexing: http://www.tutorialized.com/view/tutorial/Optimizing-MySQL-Queries-and-Indexes/7139
0
 
PapertripCommented:
Ah, so you're finally working on the indexes =).

:D
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
XK8ERAuthor Commented:
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.
slow_query_log
slow_query_log_file=/opt/mysql_slow.log
long_query_time = 4

Open in new window

0
 
johanntagleCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
Additionally, you may want to log queries not using indexes:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now