MySQL queries - aliases and record counts

I'm working on a very old database for a client who sells books online. We've got 15000 records to sift, referenced across different tables - the site was querying all 15k rows (using Classic ASP, argh) and it was running very slowly indeed.

I've now used a JOIN query to join the reference table to the records we want to display, and used LIMIT and OFFSET to query 100 rows at a time - all is much faster.

Two things I want to do:

• The two tables I've joined have the same column name (BookID) which is required to get further info on books. I know you can use aliases, but as we're getting a lot of details about each title, I'd rather get only the ID as an alias and everything else could stay. Is this possible?

• Because we're now limiting our query to 100 at a time, we can't see how many total records we've got. Is there a way to 开发者_JS百科do this without opening a second connection and slowing the site down again...?

---

Fetch the record from the database trough the paging means that you will get the records from the database 100 at a time so that query will perform fast. Go Through the Following Link

http://www.4guysfromrolla.com/webtech/062899-1.html

you will get the particular amount of the records.

---

Mysql has a this FOUND_ROWS() function used in conjuction with SQL_CALC_FOUND_ROWS (see here) which will help on finding total records and consequently paginating your results...

As to your queries if two tables have the same column name then reference them as tab1.ID and tab2.ID - if these are foreign keys and therefore should be the same value then you only need one of them - in which case tab1.BookID as BookID will be more than appropriate.

---

just wondering, are the tables indexed? I've worked with SQL Server, DB2, MySQL and MS Access. I've never seen noticably slow performance on anything NEAR as few as 150,000 records when indexed.

with mysql you should run the problematic query prefixed with "DESC" or "EXPLAIN" to see how many rows its searching through and if a Index is being used.