{"flag":true,"single":true,"pageTitle":"Indexes, Unique, Primary key in Database Mysql","post":{"id":310,"user_id":"1","slug":"indexes-unique-primary-key-in-database-mysql-jvsb","title":"Indexes, Unique, Primary key in Database Mysql","body":"

Indexes in MySQL are used to make queries faster. They work like a book’s index: instead of reading every page. MySQL must scan the entire table row by row In normal.<\/p>\r\n

???? What is an Index?<\/strong><\/p>\r\n

    \r\n
  1. A data structure (usually a B-Tree) that MySQL maintains for quick lookup. MySQL can jump directly to relevant rows.<\/li>\r\n<\/ol>\r\n

    ???? Types of index?<\/strong><\/p>\r\n<\/colgroup>\r\n\r\n\r\n\r\n\r\n\r\n
    1.Normal Index<\/strong><\/td>\r\n2. Unique Index<\/strong><\/td>\r\n<\/tr>\r\n
    Allows duplicate values.<\/td>\r\nEnsures that values in the column are unique (like a constraint). ERROR if insert duplicates<\/td>\r\n<\/tr>\r\n
    Useful for: Best for columns that you frequently use in WHERE, JOIN, or ORDER BY.<\/td>\r\nUseful for: emails, usernames, national IDs, etc.<\/td>\r\n<\/tr>\r\n
    \r\n
    CREATE INDEX idx_name ON users(name);<\/code><\/pre>\r\n
    ALTER TABLE users ADD INDEX idx_name (name);<\/code><\/pre>\r\n<\/td>\r\n
    		\r\n
    CREATE UNIQUE INDEX uniq_email ON users(email);<\/code><\/pre>\r\n
    ALTER TABLE users ADD UNIQUE INDEX uniq_email (email);<\/code><\/pre>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n
     <\/p>\r\n<\/colgroup>\r\n\r\n\r\n\r\n\r\n
    3. Primary Key (special unique index)<\/strong><\/td>\r\n4. Fulltext Index<\/strong><\/td>\r\n<\/tr>\r\n
    \r\n
    Every table usually has one primary key.<\/p>\r\n
    It’s a unique index that also identifies each row.<\/p>\r\n<\/td>\r\n
    		Special index for searching words inside text. Works with MATCH() AGAINST().
    <\/strong>Best for articles, reviews, descriptions.
    <\/strong><\/td>\r\n<\/tr>\r\n
    \r\n
    ALTER TABLE users ADD PRIMARY KEY (id);<\/code><\/pre>\r\n<\/td>\r\n
    		\r\n
    ALTER TABLE reviews ADD FULLTEXT idx_title (title);<\/code><\/pre>\r\n
    SELECT * FROM reviews \r\nWHERE MATCH(title) AGAINST('coffee shop' IN NATURAL LANGUAGE MODE);<\/code><\/pre>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n
     <\/p>\r\n<\/colgroup>\r\n\r\n\r\n\r\n\r\n
    5.Composite Index<\/strong><\/td>\r\n<\/tr>\r\n
    An index on multiple columns.<\/td>\r\n<\/tr>\r\n
    \r\n
    CREATE INDEX idx_name_city ON users(name, city);<\/code><\/pre>\r\n
    SELECT * FROM users WHERE name='John' AND city='Paris';<\/code><\/pre>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n
    Example (1 million rows):<\/p>\r\n<\/colgroup>\r\n\r\n\r\n\r\n\r\n\r\n\r\n
    Query<\/strong><\/td>\r\nWithout Index<\/strong><\/td>\r\nWith Index<\/strong><\/td>\r\n<\/tr>\r\n
    WHERE id=123<\/td>\r\n1-2 seconds<\/td>\r\n1-2 mili seconds<\/td>\r\n<\/tr>\r\n
    WHERE email='abc@gmail.com'<\/td>\r\n1 s<\/td>\r\n1-3ms<\/td>\r\n<\/tr>\r\n
    WHERE name LIKE \"abc%\"<\/td>\r\n1 s<\/td>\r\n5-10ms<\/td>\r\n<\/tr>\r\n
    WHERE name LIKE \"%abc%\"<\/td>\r\n1s<\/td>\r\n1s (Index not use if %query%)<\/strong><\/span><\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n
    \r\n
     <\/div>\r\n
     <\/div>\r\n
    How to Delete Indexes<\/strong><\/div>\r\n
    \r\n
    -- Drop index created with CREATE INDEX or ALTER TABLE ADD INDEX\r\nDROP INDEX idx_name ON users;\r\n\r\n-- Drop unique index\r\nALTER TABLE users DROP INDEX uniq_email;\r\n\r\n-- Drop primary key\r\nALTER TABLE users DROP PRIMARY KEY;<\/code><\/pre>\r\n
     <\/p>\r\n
    How to Check Indexes<\/strong><\/p>\r\n
    SHOW INDEXES FROM users;<\/code><\/pre>\r\n
    Key_name <\/strong>→ index name
    Non_unique <\/strong>→ 0 = unique, 1 = not unique
    Seq_in_index <\/strong>→ column order in composite index<\/p>\r\n
    Use EXPLAIN to check if MySQL is actually using your index:<\/strong><\/p>\r\n
    EXPLAIN SELECT * FROM users WHERE email = 'abc@test.com';<\/code><\/pre>\r\n
     Summary:<\/strong><\/p>\r\n
    INDEX <\/strong>= speed booster.<\/p>\r\n
    UNIQUE INDEX <\/strong>= speed booster + ensures no duplicates.<\/p>\r\n
    FULLTEXT <\/strong>= for searching words.<\/p>\r\n
    Indexes can make queries run in milliseconds instead of seconds, especially on large tables.<\/p>\r\n
    Export Database:<\/strong><\/p>\r\n
    \/\/ Generate Schema-Only Dump (including Primary & Unique Keys)\r\nmysqldump -u rizi -p --no-data reviewsfunda > schema_only.sql \r\n\r\n\/\/ export only data insert commands\r\nmysqldump -u rizi -p --no-create-info reviewsfunda > data_only.sql \r\n\r\n# Get a full schema dump including all index definitions\r\nmysqldump -u rizi -p --no-data --skip-add-drop-table reviewsfunda > full_schema_with_indexes.sql \r\n<\/code><\/pre>\r\n
    Import Database:<\/strong><\/p>\r\n
     <\/p>\r\n<\/div>\r\n<\/div>","category_id":"23","is_private":"0","created_at":"2025-09-08T06:30:27.000000Z","updated_at":"2025-09-11T20:45:24.000000Z","category":{"id":23,"user_id":"1","name":"DATABASE","slug":"database-j4ss","parent_id":null,"created_at":"2023-05-26T03:25:05.000000Z","updated_at":"2023-05-26T03:25:05.000000Z"},"user":{"id":1,"name":"R GONDAL","email":"rizikmw@gmail.com","email_verified_at":null,"two_factor_confirmed_at":null,"current_team_id":"1","profile_photo_path":null,"created_at":"2023-03-12T10:49:33.000000Z","updated_at":"2025-01-10T12:59:00.000000Z","profile_photo_url":"https:\/\/ui-avatars.com\/api\/?name=R+G&color=7F9CF5&background=EBF4FF"}},"pageDesc":"Indexes in MySQL are used to make queries faster. They work like a book’s index: instead of reading every page. MySQL must scan the en - Indexes, Unique, Primary key in Database Mysql (Updated: September 11, 2025) - Read more about Indexes, Unique, Primary key in Database Mysql at my programming site [SITE]","categories":[]}