CODEWITHSUNDEEP
mysqlsqlperformance
user1857342
user1857342

Reputation: 1

MySQL Query Taking a Long Time

I have a pretty simple query over a table with about 14 millions records that is taking about 30 minutes to complete. Here is the query:

select a.switch_name, a.recording_id, a.recording_date, a.start_time, 
       a.recording_id, a.duration, a.ani, a.dnis, a.agent_id, a.campaign, 
       a.call_type, a.agent_call_result, a.queue_name, a.rec_stopped,
       a.balance, a.client_number, a.case_number, a.team_code
from recording_tbl as a 
where client_number <> '1234567'

Filtering on client_number seems to be the culprit and that columns does have an index. I'm not sure what else to try.

Upvotes: 0

Views: 104

Answers (6)

user2372118
user2372118

Reputation: 1

Why do you need to return 14m rows? (I'm assuming that most records do not have the ID you are searching on). If you don't need all 14m rows, add LIMIT to the end of your query. Less rows -> less memory -> faster query.

Example:

select a.switch_name, a.recording_id, a.recording_date, a.start_time, 
   a.recording_id, a.duration, a.ani, a.dnis, a.agent_id, a.campaign, 
   a.call_type, a.agent_call_result, a.queue_name, a.rec_stopped,
   a.balance, a.client_number, a.case_number, a.team_code
from recording_tbl as a 
where client_number <> '1234567'
LIMIT 1000

Would return the first 1000 rows.

And here's a comparison of how to return the top N rows across different SQL RDBMS: http://www.petefreitag.com/item/59.cfm

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Your query is doing a full table scan on the one table in the query, recording_tbl. I am assuming this is a table and not a view, because of the "tbl" prefix. If this is a view, then you need to optimize the view.

There is no need to look at the explain. An index is unlikely to be helpful, unless 99% or so of the records have a client_number of 1234567. An index might makes things work, because of a phenomenon called thrashing.

Your problem is either undersized hardware or underallocated resources for the MySQL query engine. I would first look at buffering for the engine, and then the disk hardware and bandwidth to the processor.

Upvotes: 1

beiller
beiller

Reputation: 3135

Is the table myisam or innodb? If innodb increase innodb buffer to a large amount so entire table can fit into memory. If myisam well it should automatically load into memory via OS cache buffers. Install more RAM. Install faster disk drives. These seem to be your only solutions considering you are doing an entire table scan (minus whatever client number which appears to be your testing client id?)

It takes awhile to load the tables into RAM as well so dont expect it as soon as the db starts up.

Upvotes: 1

George
George

Reputation: 1054

If Client_Number is stored as a number field then

where client_number = 1234567

May be faster if the string comparison was causing it to do a cast and possibly preventing the indexes being used.

Upvotes: 0

MrCleanX
MrCleanX

Reputation: 416

Maybe...

where client_number = '1234567'

...would be a bit faster.

Upvotes: 0

user170442
user170442

Reputation:

You can start from creating INDEX on client_number and see how it helps, but the best results you'll get when you analyze your problem using EXPLAIN command.

http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

Upvotes: 1

Related Questions