Solved

phpMyAdmin and MySQL Indexes

Posted on 2011-02-28
5
1,203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

710 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