asked on

Slow mysql join

PortletPaul

What are you expecting this query to do exactly? The group by clause (o1.useremail) does not seem to relate to the select clause (o1.orderdate, u1.id), and there are no aggregation functions.

try matching the select and group by clauses?

SELECT o1.orderdate, u1.id
FROM orders o1
LEFT JOIN usertable u1 ON o1.useremail = u1.email
WHERE o1.orderdate >= '2013-09-03'
	AND o1.orderdate < '2013-09-04'
	AND o1.ordertype NOT IN ('20', '30')
GROUP BY o1.orderdate, u1.id
ORDER BY o1.orderdate;

Open in new window

mkaranikolas

ASKER

Hello and thank you for responding

This query is a my attempt to reduce an larger query, but this the slow part of the overall problem. Basically we want to join the results of the orders query so that we can get the id from the usertable that corresponds to the emails in the group by query.

People that ran this query in the past claim that it used to run in under a minute. .. now it just gets stuck. The only difference is that we rebuilt the data folder because of a recent crash

PortletPaul

My point regarding the group by clause remains however - I know MySQL permits this but in my experience the group by should directly relate to the output.

Not that familiar with internals of MariaDB, but do you need to refresh the statistics and/or the indexes? Different row counts in the explain plan may indicate either stats/indexes need maintenance.

any change (e.g. rebuilding data folder) can have impacts, do you mean the dbms files were restored?

PortletPaul

and, does a simple count run OK? in reasonable time?

SELECT count(*)
FROM orders o1
LEFT JOIN usertable u1 ON o1.useremail = u1.email
WHERE o1.orderdate >= '2013-09-03'
AND o1.orderdate < '2013-09-04'
AND o1.ordertype NOT IN ('20', '30')

mkaranikolas

ASKER

Hi PorletPaul,

it took ~26 min to complete

count(*)
2582

MariaDB [db]> explain SELECT count(*) 
FROM orders o1
 LEFT JOIN usertable u1 ON o1.useremail = u1.email 
WHERE o1.orderdate >= '2013-09-03'       
AND o1.orderdate < '2013-09-04'       
AND o1.ordertype NOT IN ('20', '30');
+------+-------------+-------+-------+---------------------+-----------+---------+------+---------+--------------------------------------------------------------+
| id   | select_type | table | type  | possible_keys       | key       | key_len | ref  | rows    | Extra                                                        |
+------+-------------+-------+-------+---------------------+-----------+---------+------+---------+--------------------------------------------------------------+
|    1 | SIMPLE      | o1    | range | ordertype,orderdate | orderdate | 9       | NULL |    2840 | Using index condition; Using where                           |
|    1 | SIMPLE      | u1    | index | NULL                | email     | 258     | NULL | 1528598 | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+-------+-------+---------------------+-----------+---------+------+---------+--------------------------------------------------------------+

Open in new window

mkaranikolas

ASKER

those tables are innodb

MariaDB [db]> optimize table usertable;
+------------------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------------------------+----------+----------+-------------------------------------------------------------------+
| db.usertable | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| db.usertable | optimize | status | OK |
+------------------------------------+----------+----------+-------------------------------------------------------------------+

>>>> You can optimize the InnoDB tables by using this.

we tried performing joins on tables that were "copied" from the original ones... still same problem.

ALTER TABLE table.name ENGINE='InnoDB';

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mkaranikolas

ASKER

the problem was usertable was charset latin

orders charset utf8

I created a copy of usertable, alter table to use charset utf8, copied all rows from original table to the test table

ran the same query (using the new utf8 table)

~2000 results in 3.75 seconds.

Thanks,

PortletPaul

:) excellent, and one to remember!
Thanks for the grade. Cheers, Paul