Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

phpMyAdmin and MySQL Indexes

Posted on 2011-02-28
5
Medium Priority
?
1,243 Views
Last Modified: 2012-06-21
I have noticed that indexed fields are combined when creating tables using phpMyAdmin. Is it more efficient to manually create separate index keys for each column that I want indexed?

I would like an SQL query that will add the most optimal index (for performance) for the following table. This will help me to better understand this.

Table: custom_field_data
--------------------------------------
field_id : BIGINT UNSIGNED INDEX      - Field definition.
object_id : BIGINT UNSIGNED INDEX   -   Object that field data applies to
value : TEXT      - Actual field value does not need to be indexed at all.


In phpMyAdmin I have

Indexes:

keyname: field_id
type: BTREE
unique: NO
packed: NO
field:
--> field_id       cardinality=18  collation=A
--> object_id    cardinality=18  collation=A
0
Comment
Question by:numberkruncher
  • 3
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 2000 total points
ID: 35000689
I depends on how you intend to use the keys. See http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html and search for multiple-column index.
0
 
LVL 13

Author Closing Comment

by:numberkruncher
ID: 35001100
Thanks, that link was very useful.
0
 
LVL 13

Author Comment

by:numberkruncher
ID: 35001334
So presumably, would it be worth having both of the following on a performance critical table? Would it be better to just have two separately indexed fields (without any multi-index)?

keyname: field_id
type: BTREE
unique: NO
packed: NO
field:
--> field_id       cardinality=18  collation=A
--> object_id    cardinality=18  collation=A

keyname: field_object_id
type: BTREE
unique: NO
packed: NO
field:
--> object_id    cardinality=18  collation=A
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35002157
In this case I would have two separate indexes or I might even consider having them the other way round. What I mean is if you NEVER use field_id without object_id but object_id could be used on its own sometimes then I would use

KEY ( object_id, field_id )

because with object_id as the major key it can be used as if it was a separate index but the two of them together still work. If in doubt I generally split the columns into seperate indexes.
0
 
LVL 13

Author Comment

by:numberkruncher
ID: 35002797
Thanks again for your help, it is greatly appreciated!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

926 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