Announcement

Announcement Module
Collapse
No announcement yet.

Query help

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query help

    We have a table of this structure:

    CREATE TABLE `data` (
    `id` bigint(cool: NOT NULL auto_increment,
    `brand_id` bigint(cool: NOT NULL default '0',
    `pub_date` date NOT NULL default '0000-00-00',
    `title` text NOT NULL,
    PRIMARY KEY (`id`),
    KEY `brand_id` (`brand_id`),
    KEY `pub_date_2` (`pub_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

    we do the following query

    SELECT title
    FROM data
    WHERE brand_id =5 and pub_date >="2008-11-01" and pub_date < "2008-11-31"

    When we do a query and do EXPLAIN we can see it correctly limits the results to scan only those with a brand id of 5. However it scans every row with a brand_id of 5 no matter what the pub_date is. With a few million rows in this table this gets very slow. Is there anyway to stop it scanning every row for each date and only scan the ones in the date range specified in the query? As thats why there is a key on the pub_date field I thought, to prevent row scans?

    Thanks very much in advance

  • #2
    Did You try key ( brand_id, pub_date ) ?

    What does explain show when You look only for
    Quote:


    SELECT title
    FROM data
    WHERE brand_id = 5 and pub_date = "2008-11-01"

    ?

    Comment


    • #3
      Thanks,
      Do you mean combining the two keys when the index is built? I haven't tried that, is that likely to be faster than having 2 seperate keys?

      Comment


      • #4
        Oh and it shows

        id select_type table type possible_keys key key_len ref rows Extra
        1 SIMPLE data ref brand_id,pub_date_2 pub_date_2 3 const 10780 Using where

        Comment


        • #5
          It should be faster.

          Comment


          • #6
            Will it stop the massive row scans? I don't quite understand how combining indexes helps? Any resources or explanations people could point me too.
            Thanks

            Comment


            • #7
              Mysql is looking for optimal index, brand_id gives more results than pub_date_2, so mysql is using this one. You could combine brand_id and pub_date, that way mysql will use both values and check only those rows which have got right brand_id and right pub_date.

              Comment


              • #8
                Excellent thanks for your help.

                Comment

                Working...
                X