Very slow query

richie

My CPU was running at a constant 99.8%
A look in /var/log/mysql-slow-query.log
showed the query:

-----------------------------QUERY------------------------------------
SELECT * , COUNT( a_inbox.id ) AS numInInbox
FROM a_searchA
LEFT OUTER JOIN a_inbox ON a_searchA.userid = a_inbox.userid
WHERE active = '1'
GROUP BY a_searchA.userid;
-----------------------------QUERY------------------------------------

When I ran the query from phpmyadmin, it returned no result after 5 minutes. So I killed the process.

The query (note the "EXPLAIN"):

-----------------------------QUERY------------------------------------
EXPLAIN SELECT * , COUNT( a_inbox.id ) AS numInInbox
FROM a_searchA
LEFT OUTER JOIN a_inbox ON a_searchA.userid = a_inbox.userid
WHERE active = '1'
GROUP BY a_searchA.userid;
-----------------------------QUERY------------------------------------

produced this result:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE a_searchA ALL NULL NULL NULL NULL 18798 Using where; Using temporary; Using filesort
1 SIMPLE a_inbox ALL NULL NULL NULL NULL 35403

Also,
a_searchA has 18,798 records
a_inbox has 35,401 records

I'd appreciate suggestions on how to fix the query.

Thank you.

bradgrafelman

Perhaps adding a key (userid looks like a good candidate) would help you out here?

Also, are you positive you're going to use every single column in both tables? If not, don't do a "SELECT " -- this makes MySQL work the hardest. In fact, for scalability, it's wise to never use "SELECT " queries.

Roger_Ramjet

It is because you have the WHERE active = '1' in there. Even if you have indexes, a boolean column like that will force a table scan: the filesort in explain. If you indexed it the query optimiser would probably ignore it and still do a table scan.

Piranha

Do you need every record returned? Then the query will return at least 35,401 records. And try to show at least 35,401 records. Especially sending all the information to PHP and handle it there takes lots of time. And normally you don't even want all the records, it's used just because it is easy to do it that way. Note that the numbers here don't handle the active column, so it might be fewer records. But it is still lots of records to handle.

Use LIMIT if you use MySQL, that would solve part of the problem.

Roger_Ramjet

Piranha, the WHERE clause is the reason that it has to access every record in the table. If the column were indexed then it would have such a low cardinality that mysql would ingore it anyway when executing the query.

Richie, which table is the 'active' column in anyway?

Piranha

Roger, I know. And if it is only the query that causes the problem then it is almost no difference if everything is returned or not. But since I have seen quite many on this forum that complain about the query when the problem is not there I think it is worth to mention. And why return more than needed, even if it would only be a small difference?

richie

Thank you for all your help, and sorry to reply late. I had a few things to shuffle.

The web app was not written by me. I guess it worked right when it was first written (little data). I thought the query could be re-written in such a way as not to change the number of output, but it doesn't seem that'd happen.

bradgrafelman says that "SELECT " is not a good idea. Should it really matter whether one uses "SELECT " or "SELECT col1, col2"? After all, all that matters to mysql is determining whether a record matches given conditions. But I could be wrong.

Roger Ramjet, "active" is in a_searchA.

Piranha suggested using LIMIT. That's true, but in this case, all record count is needed.

I'll post back when I find something that works from your suggestions.

Thx.

Roger_Ramjet

Ok, taking it one step at a time.

Using SELECT * here means every column from both tables - is that what you really want?
Assuming this is 1:M a_searchA:a_inbox , then you are going to get 35,401 rows returned with data from a_searchA duplicated when there is more than one corresponding match in a_inbox.

Also, since the rows in a_inbox probably have different data in some columns, your count will be 1 in every row.

So please explain, with full examples, exactly what this query is for: what is the page script for and what does it try to display? Remember, more information is better than not enough.