Slow mysql query. Any tips?

Question

I have the below query... It works but it runs extremely slow. Was hoping someone might be able to give me a few tips to improve execution time?

SELECT tb_clients.*, tb_clients_phone_fax.*
FROM tb_clients, tb_clients_phone_fax
WHERE tb_clients.client_id=tb_clients_phone_fax.client_id
AND MATCH (client_company,client_description,client_keywords) AGAINST ('test') > 0
AND CONCAT(client_address,' ',client_city,', ',client_state,' ',client_zip) LIKE '%brooklyn%'
LIMIT 10;

EDIT:

Here is the table info:

CREATE TABLE `tb_clients` (
  `client_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `client_company` varchar(254) NOT NULL,
  `client_address` varchar(254) NOT NULL,
  `client_street` varchar(254) NOT NULL,
  `client_city` varchar(254) NOT NULL,
  `client_state` varchar(254) NOT NULL,
  `client_zip` varchar(45) NOT NULL,
  `client_email` varchar(254) NOT NULL,
  `client_website` varchar(254) NOT NULL,
  `client_description` text NOT NULL,
  `client_keywords` text NOT NULL,
  PRIMARY KEY (`client_id`) USING BTREE,
  FULLTEXT KEY `client_company` (`client_company`,`client_description`,`client_keywords`)
) ENGINE=MyISAM AUTO_INCREMENT=68347 DEFAULT CHARSET=latin1;

AND

CREATE TABLE `tb_clients_phone_fax` (
  `client_phone_fax_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int(11) unsigned NOT NULL,
  `client_phone_1` varchar(45) NOT NULL,
  `client_phone_2` varchar(45) NOT NULL,
  `client_phone_3` varchar(45) NOT NULL,
  `client_fax_1` varchar(45) NOT NULL,
  `client_fax_2` varchar(45) NOT NULL,
  `client_fax_3` varchar(45) NOT NULL,
  PRIMARY KEY (`client_phone_fax_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=33944 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Answer 1

tb_clients.client_id and tb_clients_phone_fax.client_id should be indexed.

But the main problem seems to be the two strings comparisons, MATCH and the LIKE with the CONCAT. Are you sure there is no other war around it? like, avoiding concatenating all the address fields before doing the LIKE statement?

UPDATE: It seems that tb_clients_phone_fax.client_id is not indexed, it would improve the performance if it's indexed.

Answer 2

Without seeing the table schema it's hard to say for sure, but I would assume it's because you're not searching on any indicies.

Try checking the MySQL manual on full-text searches: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html For a better answer you'll most likely need to post your schema and indicies.

Answer 3

You should start by making sure that tb_clients has an index for client_id, and tb_clients_phone_fax has an index on client_id.

Then try adding in indexes for client-company,client-description,client-keywords.

However, I am willing to bet that the major slow down is coming for the comparison you are doing on the concatenated client_address,client_city,client_state,client_zip, so try to find some other way to do this comparison without a concat. Can you add a field to the table that holds the data of those items concatenated, and then just compare on that field? (Ugly I know)

Answer 4

Knowing nothing about your situation, my first thought, aside from the indexing which has been mentioned, is: how many rows are in those two tables? Is one of them only a few thousand while the other is ten million? Assuming that your query performance isn't due to joining with a very large data set, then:

As others have said, check that your columns are indexed. Also, why not fulltext index the columns you're concatenating and then use a second MATCH() AGAINST() in your query instead of CONCAT() and LIKE?

What about updating your code to check for, say, a zip-code pattern and then writing your query accordingly? Or, say, if it's not a zip-code pattern, then don't bother searching that one column.

One thing that's worked well for me in high-volume situations where an entire (large) table needs to be searched is to create a column that contains the concatenation of the whole table. I put a fulltext index on that column and use MATCH() AGAINST() and have very fast query times--so in my experience, fulltext indexing gets the job done.

Now, if you do have a very large table, then joining in real-time might become unacceptable. At this point, you have a few options, but all of them a variants of doing that joining in the background and storing that result for the benefit of the end-user experience.

Answer 5

I would create a view for the table tb_clients that had a 'fake' column that would contain your concatenated information.

In addition, I would index the previously mentioned id columns.

then you would use the same basic query, replacing the view for the table and omitting the concat ( since it is being done by the view as an aggregate column )

I am 5 minutes from off work so I can't go into a great deal of detail. If you need code, example sql etc let me know and I will put something together for you later tonight or tomorrow.

Answer 6

One very small tip:

CONCAT(client_address,' ',client_city,', ',client_state,' ',client_zip) LIKE '%brooklyn%'

Is the whole CONCAT necessary? Do you have many clients with brooklyn inside their state or zip code?

(Also, you realize that this would match an address like

1342 Brooklyn Street
Foobar, MI 98765

Is that intentional?)

Answer 7

The first step when debugging slow queries is running EXPLAIN SELECT ... and looking at what MySQL is actually doing, instead of guessing. But yes, it does look like an index on tb_clients_phone_fax.client_id would help, as others have said.