Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14

Thread: mysql indexes

  1. #1
    New Coder
    Join Date
    Nov 2011
    Posts
    97
    Thanks
    2
    Thanked 0 Times in 0 Posts

    mysql indexes

    Hello, the parameter of mysql Handler read rnd next is 23 G and Handler read rnd 9.3 M it is way too high I think, I've also added some index in the fields used by the queries but it still is so big what did I do wrong ?
    The site page that use that table load very slow

    thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    And you expect us to GUESS what your MySQL tables look like? What indexes your are using? What your SQL query looks like?

    I'm afraid my crystal ball is not working that well.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Nov 2011
    Posts
    97
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have a table 'data' with 4 columns (id, name, surname, city) and the query is

    Code:
    SELECT * FROM data WHERE id=512
    can that query take advantage of this index or do I have to index only id ?
    Code:
    ALTER TABLE data ADD INDEX keys (id,name,surname,city)
    Last edited by paperino00; 04-29-2013 at 01:19 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Yes, it will use the index.

    *BUT* queries such as
    Code:
    SELECT * FROM data WHERE name='john'
    or
    SELECT * FROM data WHERE surname='jones'
    or
    SELECT * FROM data WHERE city='boston'
    could *NOT* use the index!!!

    That's because MySQL can only use an index in LEFT-TO-RIGHT ORDER, with *NO* skipping of fields.

    In other words it could use the index for a query that used:
    id
    id and name
    id and name and surname
    id and name and surname and city

    But *ONLY* those combinations.

    So it's probably NOT a good idea to use a single index like that.

    *PROBABLY* you should have these three indexes:
    CREATE UNIQUE INDEX data_id ON data(id)
    CREATE INDEX data_surname_name ON data(surname, name)
    CREATE INDEX data_city ON data(city)

    However, if id is your primary key, then you should not have a separate UNIQUE INDEX for it. The primary key is already a unique index.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Nov 2011
    Posts
    97
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Now extra field show using where. why it doesn't show using index too ?

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    One more time: No code, no help.

    I have no idea what "extra field" you are talking about. Or what that has to do with using an index or not.

    SHOW YOUR CODE.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Nov 2011
    Posts
    97
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I mean the extra field when you run EXPLAIN.

    One query uses the index but under "ref" (in EXPLAIN) there is NULL, what does it mean ?

    How can I add an useful index for this query since MATCH AGAINS require FULLTEXT ?

    Code:
    SELECT photo.*,users.userid,_users.username FROM photo,users  
    WHERE  photo.status='Exists' 
    AND photo.active='yes' 
    AND MATCH(photo.title,photo.tags) AGAINST ('+number +two' IN BOOLEAN MODE)  
    AND photo.userid = users.userid 
    ORDER BY date_added DESC ;
    thanks

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Okay.

    The "Extra" in EXPLAIN has nothing to do with whether indexes are being used or not.

    For that, you must look at "possible_keys" and "key".

    ************

    And as for the index used for FULLTEXT: Show your CREATE TABLE statement.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Nov 2011
    Posts
    97
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Code:
    CREATE TABLE `photo` (
    `tags` mediumtext NOT NULL,
    `status` enum('Successful','Processing','Failed') NOT NULL,
    `title` text,
    `userid` int(11) NOT NULL
    ) ENGINE = MyISAM AUTOINCREMENT=960

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    And therein lies your problem.

    You haven't defined any FULLTEXT index, at all, so MySQL has no choice but to scan all rows.

    Try this:
    Code:
    CREATE TABLE photo (
        tags mediumtext NOT NULL,
        status enum('Successful','Processing','Failed') NOT NULL,
        title text,
        userid int NOT NULL,
        KEY (userid), 
        FULLTEXT( title, tags )
    ) ENGINE = MyISAM;
    MySQL won't enforce the referential integrity of the userid since this is a MyISAM table, but still putting an index on userid could well help the JOIN performance.
    Last edited by Old Pedant; 05-01-2013 at 10:52 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New Coder
    Join Date
    Nov 2011
    Posts
    97
    Thanks
    2
    Thanked 0 Times in 0 Posts
    What if my query requires both fulltext and non-fulltext indexes like this ?

    Code:
    CREATE TABLE `photo` (
    `tags` mediumtext NOT NULL,
    `status` enum('Successful','Processing','Failed') NOT NULL,
    `title` text,
    `comments` varchar(255),
    `userid` int(11) NOT NULL
    ) ENGINE = MyISAM AUTOINCREMENT=960
    
    SELECT * FROM photo WHERE MATCH (tags,title) AGAINST ('Try + query ' IN BOOLEAN MODE) AND userid = '100' AND comments = 'like';

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    I *GAVE* you the answer to that. I *DID* create an index on userid. *READ* what I said:
    Code:
        ...
        KEY (userid), 
       ...
    ...but still putting an index on userid could well help the JOIN performance.
    Even in the absence of a JOIN it will help.

    But there is no point in putting an index on comments if you are going to do
    Code:
      
      ... AND comments LIKE '%like%' ...
    because MySQL can't use an index with the LIKE operator.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    New Coder
    Join Date
    Nov 2011
    Posts
    97
    Thanks
    2
    Thanked 0 Times in 0 Posts
    if the query requires both fulltext and regular index mysql only uses the fulltext index and it has to perform a filesort for the other conditions. How can I fix this ?

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Ahhh...NOW I see what you meant. SORRY!

    Do it in steps:

    Code:
    SELECT P.* 
    FROM photo AS P,
         ( SELECT pk FROM photo 
            WHERE userid = '100' AND comments = 'like' ) AS X
    WHERE P.pk = X.pk 
    AND MATCH (P.tags,P.title) AGAINST ('Try + query ' IN BOOLEAN MODE)
    where pk is the Primary Key field of your photos table.

    I *think* that should do it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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