What is the best way to count rows in a mySQL complex table

I have a table with the following fields (for example);

id, reference, customerId.

Now, I often want to log an enquiry for a customer.. BUT, in some cases, I need to filter the enquiry based on the customers country... which is in the customer table..

id, Name, Country..for example

At the moment, my application shows 15 enquiries per page and I am SELECTing all enquir开发者_开发知识库ies, and for each one, checking the country field in customerTable based on the customerId to filter the country. I would also count the number of enquiries this way to find out the total number of enquiries and be able to display the page (Page 1 of 4).

As the database is growing, I am starting to notice a bit of lag, and I think my methodology is a bit flawed!

My first guess at how this should be done, is I can add the country to the enquiryTable. Problem solved, but does anyone else have a suggestion as to how this might be done? Because I don't like the idea of having to update each enquiry every time the country of a contact is changed.

Thanks in advance!

It looks to me like this data should be spread over 3 tables

customers enquiries countries

Then by using joins you can bring out the customer and country data and filter by either. Something like.....

SELECT 
enquiries.enquiryid,
enquiries.enquiredetails,
customers.customerid,
customers.reference,
customers.countryid,
countries.name AS countryname
FROM
enquiries
INNER JOIN customers ON enquiries.customerid = customers.customerid
INNER JOIN countries ON customers.countryid = countries.countryid
WHERE countries.name='United Kingdom'

You should definitely be only touching the database once to do this.

Depending on how you are accessing your data you may be able to get a row count without issuing a second COUNT(*) query. You havent mentioned what programming language or data access strategy you have so difficult to be more helpful with the count. If you have no easy way of determining row count from within the data access layer of your code then you could use a stored procedure with an output parameter to give you the row count without making two round trips to the database. It all depends on your architecture, data access strategy and how close you are to your database.