Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

SQL Tuning

hi_irfhi_irf ContributorCurrent User Role Beginner
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: <union1,2>
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]

Comments

  • TroyTroy Entrant Inactive User Role Beginner
    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;</pre>

    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.