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
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.
-
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%’.
-
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_listWHERE major_name LIKE ‘%teststring%’
To get the total number of records.
And the data query:
SELECT *FROM unit_history uhINNER JOIN unit_auth_list ual ON uh.serial = ual.serialWHERE ual.major_name LIKE '%testsearch%'ORDER BY ual.major_name DESC ,uh.dateLIMIT 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.
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