MySQL Statement Takes More Than Minute to Execute

MySQL statement takes more than minute to execute

Note, this is not suggesting for a minute to use MyISAM. I use that only to get my ids, min,max, and count to line up. So ignore the engine, please.

create table ratings
(   id int auto_increment primary key,
    thing int null
)engine=MyISAM;
insert ratings (thing) values (null),(null),(null),(null),(null),(null),(null),(null),(null);
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;

insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;

insert ratings (thing) select thing from ratings;
insert ratings (thing) select thing from ratings;

I now have 4.7M rows

select count(*),min(id),max(id) from ratings;
+----------+---------+---------+
| count(*) | min(id) | max(id) |
+----------+---------+---------+
|  4718592 |       1 | 4718592 |
+----------+---------+---------+
select * from `ratings` order by id limit 499500, 500;
-- 1 second on a dumpy laptop

.

explain select * from `ratings` order by id limit 499500, 500;
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | ratings | ALL  | NULL          | NULL | NULL    | NULL | 4718592 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+----------------+

.

explain select * from `ratings` where id>=499501 limit 500;
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra                 |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+
|  1 | SIMPLE      | ratings | range | PRIMARY       | PRIMARY | 4       | NULL | 4198581 | Using index condition |
+----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+

Moral of the story may be to use a where clause.

One cannot rule out the possibility of a deadlock.

SELECT statement taking more than 1 minute to execute

You should concentrate on the WHERE part and specifically you should have an index created on email column since you are having a filter condition on it WHERE email = 'xxx@xx.com'

CREATE INDEX idx_email ON accounts (email(30));

You can use an ALTER statement to do so

ALTER TABLE accounts ADD INDEX idx_email (email(30));

Per your comment since it's a TEXT type column you need to specify the index length. See MySQL documentation http://dev.mysql.com/doc/refman/5.0/en/create-index.html. Also, you may consider looking into FULLTEXT search indexing feature

SQL query is taking more than 4 minutes

SELECT
    pvl.name_parametre_value_parametre_value_lang,
    pv.id_parametre_value,

    pipv.id_parametre_value as pipv_id_parametre_value
FROM
    ps_imp_combinaison_parametre_value_6 cpv
    LEFT JOIN ps_imp_combinaison_6 ic ON ic.id_combinaison = cpv.id_combinaison
    LEFT JOIN ps_imp_parametre_value pv ON pv.id_parametre_value = cpv.id_parametre_value
    LEFT JOIN ps_imp_parametre_value_lang pvl ON pvl.id_parametre_value = pv.id_parametre_value
    LEFT JOIN ps_imp_parametre p ON p.id_parametre = pv.id_parametre
    LEFT JOIN ps_imp_product_impression_parametre_value pipv ON pipv.id_parametre_value = pv.id_parametre_value and pipv.id_product_impression = 63
WHERE
    p.id_nom_domaine = 6
    AND
    pvl.id_lang_domaine = 18
    AND
    ic.id_product_impression = 63
GROUP BY
    ic.id_combinaison,
    cpv.id_parametre_value
ORDER BY
    ic.id_combinaison,
    p.id_parametre 
LIMIT
    0, 50

How can I optimize this query, takes more than a min to execute

The planner, most probably, is not using the limit hint to eliminate rows from order table before the join. So the server has to do the join for all rows and then return just a few.

Try this:

select o.* from
(select * order order by id desc limit 100) o
inner join product p 
on o.product_id=p.id 
inner join person per 
on o.person_id=per.id 
order by o.id desc limit 100;

EDIT: This will work only if there is a constraint guaranteeing that corresponding rows are present in Product and Person tables.

Fetch records that took more than two minutes to process?

All you need to do is remove that second SELECT keyword (which shouldn't be there*), and switch from an integer to a time for the last part.

Then, switch the arguments to TIMEDIFF, assuming processed_at is expected to come before completed_at in time. This function does a − b, not b − a.

SELECT *
  FROM `queue_items`
 WHERE TIMEDIFF(`completed_at`, `processed_at`) > TIME('00:02:00');

You could also rewrite the condition a little bit to be more expressive:

SELECT *
  FROM `queue_items`
 WHERE `completed_at` > TIMESTAMPADD(SECOND, 120, `processed_at`);

I haven't gone into any detail about which would be more efficient, as you're going to need a discussion on indexes and whatnot to make any substantial difference there anyway.

* Wrapping it in parentheses turned it into a sub-query, which is not desirable or needed here.

Query takes a very long time to execute

To make the query faster in your case there have three things you can do.
Don't know it will help you or not but According to SQL optimizing query concepts, it must help.

1. Create CLUSTERED INDEX for all your tables. Cluster index makes the SELECT query faster at 30:70 of ratio.
2. You should make column list instead of STAR statement (avoid SELECT *). Extra columns make BUFFER-POOL heavy.
3. You can use VIEW instead of the query. Because VIEW during JOIN statements is better than the normal query.

From above all of three, you must try first option (CLUSTER INDEX), which will really improve performance.
Hope this will be helpful.

MySQL query takes 2 minutes

You can speed it up a bit using UNION ALL rather than UNION:

SELECT cd.*
FROM ((SELECT cd.*, 'CandlestickData' AS SOURCE
       FROM CandlestickData cd
      ) UNION ALL
      (SELECT cd.*, 'CandlestickDataHist1' AS SOURCE
       FROM CandlestickDataHist1 cd
      ) UNION ALL
      (SELECT cd.*, 'CandlestickDataHist2' AS SOURCE
       FROM CandlestickDataHist2 cd
      )
     ) cd
ORDER BY cd.MainKey DESC;

This will still require sorting all the data. However, it will remove the overhead for removing duplicates. I might venture that it would be about 50% faster.

If you really want to speed the query, you need to store all the data in a single table. Then you can create an index for the order by key.

---

Related Topics