Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » mysql join optimization question
mysql join optimization question [message #1273] Fri, 18 May 2007 23:18 Go to next message
btuchten is currently offline btuchten
Messages: 2
Registered: May 2007
Junior Member
Hi,

Could someone take a look at this and tell me why it might be slower than I expected? The search of course is slow the first time, and after that it gets cached ( and much faster ). But I am looking to speed up the initial search.

mysql> EXPLAIN select * FROM unit_history LEFT OUTER JOIN unit_auth_list  ON `unit_history`.`serial`=`unit_auth_list`.`serial` where 1;
+----+-------------+----------------+--------+---------------+--------+---------+------------------------------+--------+-------+
| id | select_type | table          | type   | possible_keys | key    | key_len | ref                          | rows   | Extra |
+----+-------------+----------------+--------+---------------+--------+---------+------------------------------+--------+-------+
|  1 | SIMPLE      | unit_history   | ALL    | NULL          | NULL   | NULL    | NULL                         | 172814 |       | 
|  1 | SIMPLE      | unit_auth_list | eq_ref | serial        | serial | 22      | mgmt_db.unit_history.serial  |      1 |       | 
+----+-------------+----------------+--------+---------------+--------+---------+------------------------------+--------+-------+
2 rows in set (0.00 sec)

mysql> select count(*) FROM `unit_history` 
LEFT OUTER JOIN `unit_auth_list` ON `unit_history`.`serial`=`unit_auth_list`.`serial` 
WHERE (`unit_auth_list`.`major_name` LIKE '%testsearch%' ) ORDER BY `unit_auth_list`.`major_name` DESC ,`unit_history`.`date`;
+----------+
| count(*) |
+----------+
|    33772 | 
+----------+
1 row in set (9.89 sec)


mysql> SELECT * FROM `unit_history` 
LEFT OUTER JOIN `unit_auth_list` ON `unit_history`.`serial`=`unit_auth_list`.`serial` 
WHERE (`unit_auth_list`.`major_name` LIKE '%testsearch%' )
 ORDER BY `unit_auth_list`.`major_name` DESC ,`unit_history`.`date` DESC LIMIT 0,30
.....
output snipped out
.....
30 rows in set (17.25 sec)


Thanks!

-Brian
Report message to a moderator
Re: mysql join optimization question [message #1275 is a reply to message #1273 ] Sat, 19 May 2007 05:24 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
In your first COUNT(*) query:
1.
You can start by dropping the ORDER BY clause. COUNT(*) is counting the nr of records and doesn't care about if they are ordered or not.

2.
Do you really want a LEFT JOIN on this query?
The way you have written it means that the result is essentially an inner join. The fact that you put the condition major_name which is in the right table in the WHERE clause is what forces this. The problem is that if you use LEFT JOIN then you are forcing mysql to use the join order first table(unit_history)->second table(unit_auth_list).
While your WHERE is defining that you want to find records WHERE unit_auth_list.major_name LIKE '%teststring%'.

3.
a LIKE '%teststring' with a wildcard in the beginning is never good performance because an index can't be used properly if you have a wildcard in the beginning. So don't expect miracles with that type of query.

My suggestion (but you will have test that it gives you the desired result):
The first query:
SELECT
  COUNT(*)
FROM
  unit_auth_list
WHERE
  major_name LIKE '%teststring%'

To get the total number of records.

And the data query:
SELECT
  *
FROM
  unit_history uh
INNER
  JOIN unit_auth_list ual
    ON uh.serial = ual.serial
WHERE
  ual.major_name LIKE '%testsearch%'
ORDER BY
  ual.major_name DESC
  ,uh.date
LIMIT
  0,30


Make sure that you have a combined index on table unit_auth_list on the columns(major_name, date).
And that you have an index on table unit_history on column (serial).
That way mysql can start the execution of the query with table unit_auth_list and then find the corresponding records in unit_history. And that should save you some time.

BTW: don't use 'date' as the column name for a DATE column. Since DATE is a column type the word date is reserved in the SQL language. Mysql as basically the only DBMS is allowing it. But you do yourself a favour if you learn now the reserved words in SQL.
Report message to a moderator
Re: mysql join optimization question [message #1277 is a reply to message #1273 ] Sat, 19 May 2007 11:01 Go to previous message
btuchten is currently offline btuchten
Messages: 2
Registered: May 2007
Junior Member
Thanks for the reply!

I was able to get my searches way down from 12 seconds to less than 1 second. Woot!

Thanks again!

-Brian
Report message to a moderator
Previous Topic:Every week DB tasks
Next Topic:Performance query count and subselect
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 03:26:37 EDT 2009

Total time taken to generate the page: 0.00574 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.