Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Posts
    7

    Unanswered: Help with select distinct

    Hello, I'm running MySQL 5.1.46 on Linux. It works fine. I have a larger database that has one 700MB table. Its slow because of the way to do my selects. I'm not very good at optimizing things so I don't know how to improve it.

    For example when I do:
    select distinct channel_num from Track where account_num = '123'

    I get this for "show processlist" and of course it takes like 500 seconds:
    Copying to tmp table

    How can I improve this?

    channel_num along with some text fields are pretty much what makes up the table.

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    what indexes are defined on the table?

  3. #3
    Join Date
    Jul 2010
    Posts
    7
    Theres a bunch of indexes, but I don't know if they're helping or hurting the problem. For example theres a field account_num, video_num, channel_num and some dates which all have a unique index. Theres 11 indexes on this table. Is that too much?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kenw232 View Post
    Is that too much?
    for a SELECT, no

    please do a SHOW CREATE TABLE for this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2010
    Posts
    7
    The table is called Track. This is it below.

    Track | CREATE TABLE `Track` (
    `track_num` int(8) NOT NULL AUTO_INCREMENT,
    `track_type` varchar(32) DEFAULT NULL,
    `account_num` int(8) DEFAULT NULL,
    `video_num` int(8) DEFAULT NULL,
    `channel_num` int(8) DEFAULT NULL,
    `channel_name` varchar(255) DEFAULT NULL,
    `channel_name_internal_label` varchar(255) DEFAULT NULL,
    `video_title` varchar(254) DEFAULT NULL,
    `video_name_internal_label` varchar(255) DEFAULT NULL,
    `video_date` date DEFAULT NULL,
    `full_datetime` varchar(64) DEFAULT NULL,
    `ip` varchar(32) DEFAULT NULL,
    `video_filename` varchar(254) DEFAULT NULL,
    `referrer` varchar(254) DEFAULT NULL,
    `embed_autostart` int(1) DEFAULT NULL,
    `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`track_num`),
    KEY `key_account_video_num` (`account_num`,`video_num`),
    KEY `key_account_num` (`account_num`),
    KEY `key_video_num` (`video_num`),
    KEY `key_account_num_channel` (`account_num`,`channel_name`),
    KEY `key_account_num_video_date` (`account_num`,`video_date`),
    KEY `key_account_num_type` (`account_num`,`track_type`),
    KEY `key_channel_num` (`channel_num`)
    ) ENGINE=MyISAM AUTO_INCREMENT=3947519 DEFAULT CHARSET=latin1

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see the index called key_account_num_channel?

    that would really work well for this query if it was indeed an index on account_num and channel_num

    unfortunately it's on account_num and channel_name

    so you will have to add yet another index --

    ALTER TABLE
    ADD INDEX key_account_num_channel_num ( account_num , channel_num )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    p.s. some of your indexes are redundant, but that only hurts INSERTs, UPDATEs, and DELETEs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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