Slow mysql join

IS THE DIFFERENT CHARSET from these two tables causing the slow performance?


Server:                  MariaDB
Server version:            10.0.4-MariaDB-log MariaDB Server
Protocol version:      10

Linux Box with over 100GB RAM, MULTIPLE RAID HD,


RUNNING THIS QUERY IS IN THE STATE "Copying to tmp table" FOR OVER 10 MIN (WE ALWAYS KILL IT) THIS IS THE EXPLAIN FOR IT


MariaDB [db]> explain 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.useremail
    -> order by o1.orderdate;
+------+-------------+-------+-------+---------------------+-----------+---------+------+---------+---------------------------------------------------------------------+
| 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; Using temporary; Using filesort |
|    1 | SIMPLE      | u1    | index | NULL                | email     | 258     | NULL | 1400277 | Using where; Using index; Using join buffer (flat, BNL join)        |
+------+-------------+-------+-------+---------------------+-----------+---------+------+---------+---------------------------------------------------------------------+

I don't know if it is worth mentioning but I ran the explain, after killing the process processing that query,
 now it shows more rows in the 2 row??? even more than the actual count(*) for usertable

2nd explain run...

+------+-------------+-------+-------+---------------------+-----------+---------+------+---------+---------------------------------------------------------------------+
| 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; Using temporary; Using filesort |
|    1 | SIMPLE      | u1    | index | NULL                | email     | 258     | NULL | 1528598 | Using where; Using index; Using join buffer (flat, BNL join)        |
+------+-------------+-------+-------+---------------------+-----------+---------+------+---------+---------------------------------------------------------------------+






MariaDB [db]> explain select o1.orderdate, o1.useremail
    -> from orders o1
    -> where o1.orderdate>='2013-09-03' and o1.orderdate<'2013-09-04'
    -> and o1.ordertype not in ('20','30')
    -> group by o1.useremail
    -> order by o1.orderdate;
+------+-------------+-------+-------+---------------------+-----------+---------+------+------+---------------------------------------------------------------------+
| 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; Using temporary; Using filesort |
+------+-------------+-------+-------+---------------------+-----------+---------+------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)



MariaDB [db]> explain select o1.orderdate, o1.useremail
    -> from orders o1
    -> where o1.orderdate>='2013-09-03' and o1.orderdate<'2013-09-04'
    -> and o1.ordertype not in ('20','30')
    -> group by o1.useremail
    -> order by o1.orderdate;
   
+---------------------+---------------------------------------+
| orderdate           | useremail                             |
+---------------------+---------------------------------------+
| 2013-09-03 00:00:01 | xxxxx@aol.com                         |
| xxxxxxxxxxxxxxxxxx  |  xxxxxxxxxxxxxxxxxxx                  |
| 2013-09-03 23:52:23 | dfffffff@yahoo.com                    |
| 2013-09-03 23:55:29 | tttttttt@hotmail.com                  |
+---------------------+---------------------------------------+
1973 rows in set (0.03 sec)





MariaDB [db]> analyze table orders;
+---------------------------------+---------+----------+----------+
| Table                           | Op      | Msg_type | Msg_text |
+---------------------------------+---------+----------+----------+
| db.orders                       | analyze | status   | OK       |
+---------------------------------+---------+----------+----------+
1 row in set (1.64 sec)

MariaDB [db]> analyze table usertable;
+------------------------------------+---------+----------+----------+
| Table                              | Op      | Msg_type | Msg_text |
+------------------------------------+---------+----------+----------+
| db.usertable                       | analyze | status   | OK       |
+------------------------------------+---------+----------+----------+
1 row in set (0.78 sec)



MariaDB [db]> select count(*) from orders;
+----------+
| count(*) |
+----------+
|  3696512 |
+----------+


MariaDB [db]> desc orders;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| transactionid       | varchar(255) | NO   | PRI |         |       |
| orderdate           | datetime     | YES  | MUL | NULL    |       |
| useremail           | varchar(255) | YES  | MUL | NULL    |       |
| ordertype           | int(11)      | YES  | MUL | NULL    |       |
| XXX MORE XXX        | XXXXX        |      |     |         |       |
+---------------------+--------------+------+-----+---------+-------+


CREATE TABLE `orders` (
  `transactionid` varchar(255) NOT NULL DEFAULT '',
  `orderdate` datetime DEFAULT NULL,
  `useremail` varchar(255) DEFAULT NULL,
  `ordertype` int(11) DEFAULT NULL,
  >>>>>>>> MORE FIELDS
  PRIMARY KEY (`transactionid`),
  KEY `transindex` (`transactionid`),
  KEY `email` (`useremail`(10)),
  KEY `ordertype` (`ordertype`),
  KEY `orderdate` (`orderdate`),
  >>>>>>>> MORE KEYS
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC







MariaDB [db]> select count(*) from usertable;
+----------+
| count(*) |
+----------+
|  1409945 |
+----------+
1 row in set (0.68 sec)


MariaDB [db]> desc usertable;
+----------------------+---------------------+------+-----+---------+----------------+
| Field                | Type                | Null | Key | Default | Extra          |
+----------------------+---------------------+------+-----+---------+----------------+
| email                | varchar(255)        | YES  | MUL | NULL    |                |
| id                   | int(11)             | NO   | PRI | NULL    | auto_increment |
| XXX MANY MORE        |                     |      |     |         |                |              
+----------------------+---------------------+------+-----+---------+----------------+


CREATE TABLE `usertable` (
  `email` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  >>>>> MORE FIELDS,
  PRIMARY KEY (`id`),
  KEY `email` (`email`),
  >>>>> MORE KEYS
) ENGINE=InnoDB AUTO_INCREMENT=1415409 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
mkaranikolasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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

0
mkaranikolasAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PortletPaulfreelancerCommented:
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')
0
mkaranikolasAuthor Commented:
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

0
mkaranikolasAuthor Commented:
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';
0
PortletPaulfreelancerCommented:
26 Minutes, damn!
all referenced fields are indexed as far as I can see, aside from checking current validity of statistics and indexes I'm not sure what to suggest.

long shot:
Is this the only query performing worse then usual, could there be any other configuration settings affected by the 'rebuild'?

ps: I didn't really mean to save the edit of your comment, but when lines wrap it's hard to read. There is a code button in the toolbar that surrounds script with tags you can use.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mkaranikolasAuthor Commented:
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,
0
PortletPaulfreelancerCommented:
:) excellent, and one to remember!
Thanks for the grade. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.