Solved

phpMyAdmin and MySQL Indexes

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 25
WordPress TK Title 8 24
Could you point how to give Bootstrap's open/ close menu effect ? 25 34
Scope of $_SESSION 17 30
This article discusses four methods for overlaying images in a container on a web page
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

910 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

23 Experts available now in Live!

Get 1:1 Help Now