Solved

phpMyAdmin and MySQL Indexes

Posted on 2011-02-28
5
1,201 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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.

735 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