Slow MySQL query

Are there any indexes (or is it indices) set on both tables, if so what are they? It's not obvious where date_posted is coming from which is probably due to my lack of mySql knowledge. Are the max and UNIX_TIMESTAMP functions/methods slowing the query down with additional overhead?

Peter B wrote:

I want to return the 20 most recent ads placed and all their book information. The ISBNs link the ads with the book info.

Currently I have

SELECT ads.isbn, max(UNIX_TIMESTAMP(date_posted)) as date1, books.*
FROM `ads`,books
WHERE ads.isbn = books.isbn
GROUP BY ads.isbn
ORDER BY date1
DESC limit 0, 20

I'm not a MySql expert, but...

I don't think you need a GROUP BY at all, nor a max function.

IF you think about it, the 20 most recent ads would be given by:

SELECT ads.*
FROM `ads`
ORDER BY ads.date_posted DESC
limit 0, 20


(I'm guessing on the limit here....I dunno how they're done in MySQl so I'm taking your code).

Now...given that books.isbn should be unique, you should be able to just join in books to this query to get your results:


SELECT ads.*, books.*
FROM `ads`,books
WHERE ads.isbn = books.isbn
ORDER BY ads.date_posted DESC
limit 0, 20

This is working very slow. Both tables "Books" and "Ads" are very big (7000+).

7000 isn't a lot of data. 7,000,000 might be a lot of data.

Regardless of the size, though, if you don't have indexes, you should. At a guess, you need one on books.isbn, one on ads.isbn, and maybe one on ads.date_posted.

jc

Can you run an EXPLAIN plan and give us the output?

Well in SQL 2000 if you specified an index based on firstname,secondname,city and searched by secondname,firstname the index would be ignored..

Similarly if you created the same index and searched by secondname or city it would be ignored. It will work with firstname.

I do agree that indexing strategies vary by implementation from DB to DB tho, it was showing an example of creating indexes and how just creating them will not always increase the speed of your query.

Ginger wrote:

Well in SQL 2000 if you specified an index based on firstname,secondname,city and searched by secondname,firstname the index would be ignored..

Assuming you mean a search of the form: WHERE secondname = X AND firstname = Y, then you're wrong. The index will be used in this situation, assuming there are enough records in the table to make index-usage more optimal over a table-scan. IF its not used in this situation, then it also wouldn't be used if you changed your WHERE clause to read WHERE firstname = Y AND secondname = X.

The MSSQL optimiser is smart enough to test alternate field-order. You can test this easily with SHOWPLAN output. Write a query doing what you think will work, test that its using the index, then rearrange the WHERE clause. The same indexes should still be used.

This limitation is one I've heard levelled at almost every RDBMS I've used. I can't think of a situation where it has been correct, unless one is talking about a version of the system which is over 10 years old.

Similarly if you created the same index and searched by secondname or city it would be ignored.

Not entirely correct. The index might be used, but only in special circumstances which are typically only ever discussed in stuff on advanced optimisation.

If the only fields being used *anywhere* in the query were contained within the index, then scanning the index could be more optimal than scanning the table. In such a case, the index will be read in preference to the table and a SHOWPLAN would show an INDEX SCAN rather than a TABLE SCAN.

How about something along these lines (and I am no sql expert)

select * from books where isbn in
(Select isbn
from ads
order by date_posted DESC
LIMIT 20);

Your problem is you are return every row back in the select because of max(UNIX_TIMESTAMP(date_posted)) as date1

amen wrote:

I don't totally agree with this statement. In certain cases on tables
with a very small amount of data an index can actually slow the query

Agreed, but only to the extent it would take the optimiser to say "no, I won't use this index cause a table-scan would be faster".