GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Irelevant results with 2M table and index

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

  • Irelevant results with 2M table and index

    my system:
    - MySQL Connector.NET 5.2, ASP.net 2 on Windows 2003 / IIS6
    - MySQL 5.0.20 on Debian
    MySQL server has 2 Dual-Core CPUs, I thing 3GHz, 4GB RAM.

    I have a 2M rows table, with an ~500K rows/month.

    table:

    DROP TABLE IF EXISTS `a7`.`pb_im`;
    CREATE TABLE `a7`.`pb_im` (
    `m_id` int(11) NOT NULL auto_increment,
    `m_date` bigint(14) NOT NULL,
    `m_from` int(11) NOT NULL default '0',
    `m_to` int(11) NOT NULL default '0',
    `m_content` text NOT NULL,
    `m_new` tinyint(1) NOT NULL default '1',
    PRIMARY KEY (`m_id`),
    KEY `to new` USING BTREE (`m_to`,`m_new`),
    KEY `from-to-date` USING BTREE (`m_from`,`m_to`,`m_id`),
    KEY `to-from` USING BTREE (`m_to`,`m_from`,`m_id`),
    KEY `to-date-from` USING BTREE (`m_date`,`m_to`,`m_from`)
    ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

    the table used to store IM messages (like MSN/ICQ). I know that storing date/time in bigint is not the best, but now it is.

    I am executing this query:

    select m_id,u_name,m_content,m_new, m_date,m_to, m_from from pb_im,users where m_new=1 and u_id=m_from and m_to=1


    explain:

    1, 'SIMPLE', 'pb_im', 'ref', 'to new,fromto date,to-from', 'to new', '5', 'const,const', 1, ''1, 'SIMPLE', 'users', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'a7.pb_im.m_from', 1, ''


    10 queries/s (with "m_to" changed)

    Sometimes, the query returned ~300-4000 irellevant rows within 8-9 seconds (slowlog).
    When I take the query from slowlog and execute it, I see correct results and execute time.


    I tried to search the net about month (also your perefect blog and forums), mysql bugs, etc with no results.

    Upgrade to 5.0.67 will help?
    something wrong with my table / indexes ?


    Thanks!

    Moshe
    Is this an emergency? Get immediate assistance from Percona Support 24/7. Click here.
Working...
X