Announcement

Announcement Module
Collapse
No announcement yet.

SQL Tuning

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

  • SQL Tuning

    We are facing huge problem with <> operator queries because log_count_arch contains more than 30 million rows and we have to extract all id's accept the one not meeting criteria (<>).

    what's the best query execution path to satisfy <> queries and re-write query option and using temporary and using filesort operations.
    For query 2 and query 3 I tried with Fulltext index as well but no luck. I also tried covering index but again no luck.

    Can someone please suggest how to improve especially get rid of using temporary and filesort.

    Query 1:

    [code]
    mysql> show create table log_count_arch\G
    *************************** 1. row ***************************
    Create Table: CREATE TABLE `log_count_arch` (
    `uid` int(11) NOT NULL DEFAULT '0',
    `lasttime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `ip` int(11) NOT NULL DEFAULT '0',
    KEY `arch_uid_idx` (`uid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    [\code]
    [code]
    mysql> show create table log_count\G
    *************************** 1. row ***************************
    Create Table: CREATE TABLE `log_count` (
    `uid` int(11) NOT NULL DEFAULT '0',
    `lasttime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `ip` int(11) NOT NULL DEFAULT '0',
    KEY `uid_idx` (`uid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    1 row in set (0.56 sec)
    [\code]
    [code]
    EXPLAIN SELECT uid, ip FROM log_count WHERE ip = '1851220710' AND uid <> '4796596'

    UNION

    SELECT uid, ip FROM log_count_arch WHERE ip = '1851220710' AND uid <> '4796596' GROUP BY uid;

    *************************** 1. row ***************************
    id: 1
    select_type: PRIMARY
    table: log_count
    type: ALL
    possible_keys: uid_idx
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 46
    Extra: Using where
    *************************** 2. row ***************************
    id: 2
    select_type: UNION
    table: log_count_arch
    type: ALL
    possible_keys: arch_uid_idx
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 30276618
    Extra: Using where; Using temporary; Using filesort
    *************************** 3. row ***************************
    id: NULL
    select_type: UNION RESULT
    table:
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: NULL
    Extra:
    3 rows in set (0.00 sec)
    [\code]


    Query 2:


    [code]
    mysql> EXPLAIN select SQL_CALC_FOUND_ROWS id, keyword from keyword
    where keyword like 'p%'
    AND count IN (2,3,4)
    ORDER BY rate DESC, time DESC LIMIT 88800,6;
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: keyword
    type: range
    possible_keys: keyword_idx,count,keyword_ft
    key: keyword_idx
    key_len: 257
    ref: NULL
    rows: 330510
    Extra: Using where; Using filesort
    1 row in set (0.00 sec)


    mysql> show create table keyword\G
    `keyword` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
    `seloff` int(10) unsigned NOT NULL DEFAULT '0',
    `seloff` int(10) unsigned NOT NULL DEFAULT '0',
    `buyoff` int(10) unsigned NOT NULL DEFAULT '0',
    `rate` int(9) NOT NULL DEFAULT '0',
    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `count` tinyint(4) DEFAULT '0'

    PRIMARY KEY (`sb_id`),
    KEY `keywords_idx` (`keyword`),
    KEY `seloff` (`seloff`),
    KEY `count` (`count`),
    FULLTEXT KEY `keyword` (`keyword_ft`)
    [\code]

    Query 3:


    [code]
    mysql> show create table uniqueproducts\G
    *************************** 1. row ***************************
    CREATE TABLE uniqueproducts
    id` int(11) NOT NULL AUTO_INCREMENT,
    title` varchar(255) DEFAULT '',
    keyword` varchar(255) DEFAULT NULL,
    comp` varchar(255) NOT NULL DEFAULT '',
    date` int(11) DEFAULT NULL;

    PRIMARY KEY (`id`),
    KEY `uid` (`uid`),
    KEY `date_idx` (`date`),

    mysql> show create table mem_pack\G
    *************************** 1. row ***************************
    CREATE TABLE mem_pack
    id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    uid` int(11) DEFAULT NULL,
    type` int(11) DEFAULT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uid` (`uid`),
    KEY `type_idx` (`type`)


    mysql>EXPLAIN SELECT o.id, o.title, o.desc, o.uid, o.keyword, o.comp FROM uniqueproducts o
    INNER JOIN mem_pack m ON o.uid = m.uid
    WHERE o.approv = 1
    AND m.type = 3
    AND match(o.title , o.keyword ) against ('+shirt +and +trouser/jeans' in boolean mode)
    GROUP BY o.uid
    ORDER BY o.date ASC, o.id ASC
    LIMIT 0, 10;

    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: o
    type: index
    possible_keys: uid
    key: uid
    key_len: 5
    ref: NULL
    rows: 2
    Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: m
    type: ref
    possible_keys: uid,type_idx
    key: uid
    key_len: 5
    ref: test.o.uid
    rows: 11
    Extra: Using where
    2 rows in set (0.00 sec)
    [\code]

  • #2
    Ok, are you sure it's the "<>" that's causing your problems? In your tables (log_count and log_count_arch) you have an index on "uid" but your best/most limiting filter ("-")is on the "ip" column which has no index. It appears that these tables store the same information. What is the difference?

    In your query:

    EXPLAIN SELECT uid, ip FROM log_count WHERE ip = '1851220710' AND uid <> '4796596'UNIONSELECT uid, ip FROM log_count_arch WHERE ip = '1851220710' AND uid <> '4796596' GROUP BY uid;

    I believe this statement will not do anything useful. What are the expected results? Why does the second part after the UNION have a "GROUP BY" clause? As I understand it... the first half gets a list of all (current?) uids (not distinct and not filtered) for a single ip and the second half gets a (distinct and not filtered) list of uids for a single ip and then filters out any duplicates (both columns) from the first part (UNION does this).

    You're basically requiring 2 full table scans (that second table has over 30 million records) to weed out a relatively small number of records. Would it make more sense to get a list of matching records for the uid and then compare to whatever you're comparing (assuming you're looking for an unused uid/ip combination).

    The first table definition in the second query is missing something. I have an idea what you're trying to do but I'm not tackling that one. There are some great ways to do full text searches and I don't think this is one of them.

    Troy

    Comment

    Working...
    X