8

I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes?

I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful.

As for why I (and others) need to do this: For example, I needed to summarize referers statistics by tld like this:

SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
    IGNORE INDEX(domain_name)
GROUP BY tld
ORDER BY c desc
LIMIT 100

...but I always have to look at what indexes are defined or determine what index will be used via Explain. It would be very handy to simply write IGNORE INDEX ALL and simply not care.

Does anyone know the syntax or a hack? (Tens of lines via MySQL definition tables are really not a shortcut).

Added from chat discussion:

Bechmark:

  • no index = 148.5 secs

  • with index = 180 secs and still running with Sending data The SSD array is so powerful, that you almost no care about data cache...

Definition for benchmark:

CREATE TABLE IF NOT EXISTS `domains_import` (
`domain_id` bigint(20) unsigned NOT NULL,
`domain_name` varchar(253) CHARACTER SET ascii COLLATE ascii_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `domains_import`
ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

ALTER TABLE `domains_import`
MODIFY `domain_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

InnoDB, the test with index (no USE INDEX() or similar) is still running, 250 secs, I just killed it.

19

It's absolutely not clear why you want this but you can use the hint USE INDEX () to tell the optimizer not to use any index. From MySQL docs: index hints

It is syntactically valid to omit index_list for USE INDEX, which means “use no indexes.” Omitting index_list for FORCE INDEX or IGNORE INDEX is a syntax error.

Your query becomes:

SELECT count(*) AS c, 
       substring_index(domain_name, '.', -1) AS tld
FROM domains_import 
       USE INDEX ()        -- use no indexes
GROUP BY tld
ORDER BY c DESC
LIMIT 100 ;

Side note: the complex expression:

SUBSTRING(domain_name, LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2) 

can be simplified from 4 function calls to 1:

SUBSTRING_INDEX(domain_name, '.', -1)
  • 1
    It was useful for me when MySQL 5.7.10 optimizer changed its querying plan for a worst one when removing some of the LEFT JOIN I had. ` USE INDEX ()` made MySQL doing a table scan on a 20K rows table and 1-to-1 JOINs instead of crossing 500 rows between two indexes. Got 20x faster. – Xenos Feb 9 '17 at 13:27
2

You could also embed WHERE 1=1

SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
WHERE 1=1
GROUP BY tld
ORDER BY c desc
LIMIT 100

ypercube just asked me

Rolando, is MySQL's optimizer so dumb that a simple always true condition will forbid the use of indexes?

Yes, but you have give MySQL a really dumb query. 1=1 would revert to the Clustered Index. Notwithstanding, there is still another way, but it requires being a little malicious to the Optimizer.

SELECT 
    count(*) as c, 
    SUBSTRING
    (
        domain_name, 
        LENGTH(domain_name) - LOCATE('.', REVERSE(domain_name)) + 2
    ) as tld
FROM `domains_import` 
WHERE domain_name = domain_name
GROUP BY tld
ORDER BY c desc
LIMIT 100

This will throw every index under the bus for sure because each row's value for domain_name much be checked. If domain_name is indexed, you must choose a column for the WHERE column_name=column_name that is not indexed at all.

I just tried this on a large table in a Staging server

mysql > explain SELECT COUNT(1) FROM VIDEO WHERE EMBEDDED_FLG=EMBEDDED_FLG;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | VIDEO | ALL  | NULL          | NULL | NULL    | NULL | 354327 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

No indexes are chosen

  • Rolando, is MySQL's optimizer so dumb that a simple always true condition will forbid the use of indexes? – ypercubeᵀᴹ Aug 11 '15 at 20:24
  • @ypercube yes, but you have to dummy-down the query enough for that to happen. – RolandoMySQLDBA Aug 11 '15 at 20:39
  • 1
    Hey, I upvoted yercube's answer myself. My answer is just another way and explains the Optimizer's loophole. – RolandoMySQLDBA Aug 11 '15 at 20:45
  • 1
    Rolando, not true: The index will be used: SQLfiddle. Even if you make something more complicated, like WHERE id+0 = id*1 the index will still be used, and an extra Using where will appear. – ypercubeᵀᴹ Aug 11 '15 at 20:47
  • 4
    @PaulWhite it would. (it's dumb but not that dumb ;) And that's maybe why Roalndo's query does not use index, the column must have been defined as NULL. – ypercubeᵀᴹ Aug 11 '15 at 21:05
0

Assuming you have these two indexes:

ADD PRIMARY KEY (`domain_id`),
ADD UNIQUE KEY `domain_name` (`domain_name`);

Then it does not matter what the optimizer does; it must scan essentially an identical amount of stuff.

Case 1: It does a table scan (or uses domain_id): It will scan (id, name) pairs, locating all the names, doing the SUBSTRING..LOCATE, GROUP BY, and finally ORDER BY. The GROUP BY and the ORDER BY probably each need a tmp table and filesort. Check EXPLAIN SELECT ... to see if it does.

Case 2: It does a index scan (of domain_name): That index actually contains (name, id) pairs -- because InnoDB implicitly puts the PK on the end of any secondary key. The rest of the processing parallels Case 1.

One thing could be different -- the size of the two BTrees. Do SHOW TABLE STATUS LIKE domains_import to see the Data_length (for Case 1) and Index_length (for Case 2). The larger BTree will be slower.

Another thing could be different -- caching. What is the value of innodb_buffer_pool_size? How much RAM do you have? Can the Data (or Index) be contained within the buffer pool. (Or will it be 37% of it, due to this being a table/index scan?) If it fits, then run the query twice. The second time will be about 10 times as fast due to not hitting the disk (caching).

If this is a one-time task, SSD will help. If not, and you can cache the entire table, then it won't help after the buffer_pool is loaded.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.