SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Optimizing a SELECT .. WHERE .. LIMIT query

    (using MySQL 5.0)

    First, the problem: I need to select different "pages" of IDs that match a small list of categories.

    Here's my table:

    Code:
    CREATE TABLE `testTable` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `category` tinyint(3) unsigned NOT NULL,
      `username` char(15) NOT NULL,
      PRIMARY KEY  (`id`),
      KEY `cat` (`category`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1
    And an EXPLAIN of my query:
    Code:
    EXPLAIN SELECT * FROM myTestable WHERE category IN(5,8,11) LIMIT 5000,20;
    
    id | select_type | table      | type  | possible_keys | key | key_len | ref   | rows  | Extra
    1  | SIMPLE      | myTestable | range | cat           | cat | 1       | null  | 49904 | Using where
    So from that we can see it's hitting 50,000 rows to get the 20 rows that I want (it's a table with 1,000,000 rows total).

    I've been using MySQL for a fair amount of time, but I haven't ever done much optimization beyond the standard "add an index on it". Is there any way to speed this query up or is it the best that can be done?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    try making a compound index on (cat, id) and dropping the index on cat.

  3. #3
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just tried it, the EXPLAIN is the same as when it has an index just on category.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    this this:
    Code:
    EXPLAIN SELECT * FROM myTestable FORCE INDEX cat WHERE category IN(5,8,11) LIMIT 5000,20;
    but replace cat with the actual name of your index.

    if that doesn't help, i'll have to work on it tomorrow when i have time to create some test data and do a bit of experimentation.

  5. #5
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did that on the new index I tried (and actually I didn't need to FORCE INDEX - when I tried it out, EXPLAIN showed that it was in fact using the new composite index you suggested, but it didn't reduce the number of rows required).

    Just to make sure, I just tried it out using FORCE INDEX, no change.

    If you have some time to play around, I appreciate it. No rush though - I'm still programming the application, I'm not planning on launching for another few months.


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
  •