SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Indexing problem

    hi all


    I am indexing email addresses, and I want to be able to search by domain name. I can’t use a regular index for this, I would need to to a query like:

    SELECT ... FROM users WHERE email LIKE '%.com'

    That query would, of course, not use indexes.

    I have heard about MySQL needs its own built-in calculated indexes. The ability to create an index based on some expression.

    So how can we solve this problem ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    indexes based on some expression? not to my knowledge

    do yourself a favour, add another column to your table, and populate it with the domain

    then you can index and search on that
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict aniltc's Avatar
    Join Date
    Nov 2006
    Location
    INDIA
    Posts
    399
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can we use triggers ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you can, but you don't need to
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot malluwood's Avatar
    Join Date
    Nov 2006
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by aniltc View Post
    can we use triggers ?
    yes you can if you use MySql 5

    see this

    MySQL needs its own built-in calculated indexes. The ability to create an index based on some expression, in our example, on REVERSE(email), and have MySQL automatically figure out how to use it. Maybe some day that will exist in MySQL, but it doesn’t currently


    You’ll want to create your table like so:

    CREATE TABLE users (
    id INT NOT NULL auto_increment,
    name CHAR(50) NOT NULL,
    email CHAR(120) NOT NULL,
    r_email CHAR(120) NOT NULL,
    PRIMARY KEY (id),
    INDEX (email),
    INDEX (r_email)
    );



    Notice that I added an extra column, r_email to store the reversed email address. You can then create a couple of triggers to keep the reversed email address up to date automatically:

    CREATE TRIGGER users_r_email_in
    BEFORE INSERT ON users
    FOR EACH ROW
    SET NEW.r_email = REVERSE(NEW.email);

    CREATE TRIGGER users_r_email_up
    BEFORE UPDATE ON users
    FOR EACH ROW
    SET NEW.r_email = REVERSE(NEW.email);

    Whenever you need to search by domain, just run your query like so:

    cooooooooooooool


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •