Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

phpMyAdmin and MySQL Indexes

Posted on 2011-02-28
5
Medium Priority
?
1,225 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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