MySQL Query In() Clause Slow on Indexed Column

MySQL Query IN() Clause Slow on Indexed Column

You are accessing 420 rows by primary key which will probably lead to an index access path. This could access 2 index pages and one data page per key. If these are in cache, the query should run fast. If not, every page access that goes to disk will incur the usual disk latency. If we assume 5ms disk latency and 80% cache hits, we arrive at 420*3*0.2*5ms=1.2 seconds which is on the order of what you're seeing.

MySQL query very slow. Count(*) on indexed column

How long is 'long'? How many rows are there in this table?

A MyISAM table keeps track of how many rows it has, so a simple COUNT(*) will always return almost instantly.

InnoDB, on the other hand works differently: an InnoDB table doesn't keep track of how many rows it has, and so when you COUNT(*), it literally has to go and count each row. If you have a large table, this can take a number of seconds.

EDIT: Try COUNT(ID) instead of COUNT(*), where ID is an indexed column that has no NULLs in it. That may run faster.

EDIT2: If you're storing the binary data of the files in the longblob, your table will be massive, which will slow things down.

Possible solutions:

1. Use MyISAM instead of InnoDB.
2. Maintain your own count, perhaps using triggers on inserts and deletes.
3. Strip out the binary data into another table, or preferably regular files.

MySQL IN queries terribly slow with subquery but fast with explicit values

Subqueries execute every time you evaluate them (in MySQL anyway, not all RDBMSes), i.e. you're basically running 7 million queries! Using a JOIN, if possible, will reduce this to 1. Even if adding indexing improves performance of those, you're still running them.

Why does Mysql query get slow when I add a column to return?

Your query involves three columns: user_id, created_at and value.

You have a few indexes on your table but none of them contains the value column. The explain seems to indicate that MySQL is using the (user_id, updated_at) index to filter matching rows but in order to generate the complete result it also scans the table to extract values from value column for matching rows.

If you create an index on (user_id, created_at, value) it could be used as a covering index... to match the where clause and and to lookup value from the index. MySQL no longer needs to touch the 22mil rows table.

---

Also, your GROUP BY is incorrect. I am guessing you need to count or sum the values for each day of year in which case you need to:

SELECT UNIX_TIMESTAMP(CAST(created_at AS DATE)) * 1000, SUM(values)
FROM t
WHERE ...
GROUP BY CAST(created_at AS DATE)
ORDER BY CAST(created_at AS DATE)

Slow mysql query with huge in clause on primary key

Here is your query:

SELECT t.* 
FROM test t 
JOIN sample s ON t.id = s.test_id 
JOIN sample_x x ON s.id = x.sample_id 
WHERE x.field_id = '321' 
AND x.value LIKE '%smth%'

Unfortunately you didn't provide the SHOW CREATE TABLE output for the test or sample_x tables.

Regardless, add this index if it doesn't already exist:

ALTER TABLE sample_x
ADD INDEX `wr1` (`sample_id`,`field_id`,`value`)

This should improve things a bit. However, using LIKE with a wildcard at the start of the string cannot be improved (at least not without fulltext indexes.)

You can also try this index, too:

ALTER TABLE sample_x
ADD INDEX `wr2` (`field_id`,`value`,`sample_id`)

This will allow the optimizer to start at the sample_x table and then work backwards towards the test table. Which it prefers to do will depend on a lot of factors.

You can remove either of these indexes with the following:

ALTER TABLE sample_x
DROP INDEX `wr1`

Or

ALTER TABLE sample_x
DROP INDEX `wr2`

Experiment to see which helps your query the most, if either of them do. When measuring performance always run the query twice, and throw out the first result. That is because the buffer cache needs to be populated the first time, and so it can take longer and won't be an accurate measure of the real improvement.

Slow MySQL query after adding multiple OR conditions to indexed column

Suppose you have a big list of people's names. And the goal is to find the first 30 Smiths (ordered by first name). The first query is fast because it is essentially doing the WHERE, ORDER BY and LIMIT all at once:

The second is messier because it is effectively done thus:

1. Find the first names of all the 'Smiths',
2. Find the first names of all the 'Joneses'
3. Sort the first names and show the first 30

There are two things to speed up your slow query:

( SELECT A.id, A.x, A.y, A.z, B.foo FROM A JOIN B ON A.id = B.id
    where (A.x = 18)
    ORDER BY A.y desc LIMIT 30 )
UNION ALL  
( SELECT A.id, A.x, A.y, A.z, B.foo FROM A JOIN B ON A.id = B.id
    where (A.x = 53)    -- Note
    ORDER BY A.y desc LIMIT 30 )  
ORDER BY A.y desc LIMIT 0, 30    -- Yes, repeated

Comments:

• STRAIGHT_JOIN is unnecessary, JOIN will happen to do the same thing
• Each subquery will use INDEX(x,y) and make use of LIMIT.
• ALL is faster than the default, and is appropriate in this case
• If you need to "paginate", the limits need to be handled as described here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or
• Any number of UNIONs can be tacked together. However, at some point, the cost of all the unions will outweigh the benefit. (It is not practical to try to predict where the cutoff is.)

It would be faster to do the LIMIT 30 before JOINing to B. That way, you would do only 30 lookups in B; my way needs 60; your original query needed lots more.

---

Related Topics