MySQL Forums
Forum List  »  Performance

Query problem
Posted by: Patrick Neeley
Date: October 22, 2004 07:32AM

I've got a table (I'll call it matchTable for this example) with 7 million records and growing rapidly. Has cols tableA_ID and tableB_ID (both are mediumints), and item_1 through item_n (tinyints with value 0 or 1). tableA_ID and tableB_ID have a keyed (combined) indexed.

I run a query that looks like this:

SELECT DISTINCT tableB_ID FROM matchTable WHERE tableA_ID IN(12,16,20,20030,254177)

My PHP page load stats tell me the script spends ~8 out the 11 total seconds (that it takes to run the entire script) doing MySQL queries... and it says that it's doing 201 of them. This is a problem as there are less than a dozen queries in the script, and less than 20 TOTAL queries are performed in the code (looped and all) in any one test run (yes, I'm sure of this.)

QUESTION 1: Does anyone know where these extra queries are coming from?

QUESTION 2: What will help? I can up the memory limits for MySQL if you think it's running out (but please tell me how I can check this.)

I really really need suggestions. This is a very complex system, the code is 100%, so I need the DB to be 100% also.

~ dr.p

Options: ReplyQuote

Written By
Query problem
October 22, 2004 07:32AM
October 22, 2004 04:07PM
October 22, 2004 04:14PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.