query failing with indexed (or not) ORDER BY
Question posted by: pedalpete
(Member)
on
August 23rd, 2008 12:23 AM
Hi MySQL masters,
I've got a bit of an issue here I'm hoping somebody will be able to help me with.
I have a large DB (1 million plus rows) and need to do a query with dual 'order by' AND limit.
Unfortunately , I haven't found a good way of doing this efficiently.
If you would like to get a better idea of what I'm running, you can check out my site www.hearwhere.com.
Basically what I have is a table for bands, and a table for concerts. Here's a simplified version of the query (so too keep it concise and hopefully not overload with unnecessary info).
My 'SELECT' statement looks like this
-
SELECT concerts.date, bands.name FROM concerts JOIN bands ON concerts.bandnum = bands.bandnum WHERE date>='2008-08-22' ORDER BY concerts.date, bands.popularity DESC LIMIT 0,30
when i remove the 'popularity' from ORDER BY, the query runs very quickly.
When i remove 'date' from ORDER BY and reverse the order of tables so that the bands table is in the FROM and the concerts table is joined, the query takes forever and crashes my server quite regularly.
Looking at my explain output, I see a few issues. particularly, it appears niether the indexes (concertDate_idx or bandsPop_idx) are being used.
-
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+---------+--------+-----------------+-----------------+---------+------------------------+---------+----------------------------------------------+
-
| 1 | SIMPLE | concerts| ALL | get_band_idx | NULL | NULL | NULL | 1233591 | Using where; Using temporary; Using filesort |
-
| 1 | SIMPLE | bands | eq_ref | link_band_idx | link_band_idx | 4 | hearwhere.shows.band | 1 | |
-
+----+-------------+---------+--------+-----------------+-----------------+---------+------------------------+---------+-------------------------------------
I have read that apparently MySQL can't use an index from a joined table, and I'm hoping that there is a nicer way of getting this query to run than to duplicate the 'popularity' field in both tables.
Any suggestions for this?
2
Answers Posted
Hi,
we could help more if you did the following:
1) post a print out of "show create table" for your tables, so we can see what indexes you have used, what data types, etc.
2) post more Explain outputs, such as the one where you say it crashes because you change the table order in the join.
-
SELECT concerts.date, bands.name FROM concerts JOIN bands ON concerts.bandnum = bands.bandnum WHERE date>='2008-08-22' ORDER BY concerts.date, bands.popularity DESC LIMIT 0,30
Above I repeat your query. The query will not necessarilly wish to use your primary keys on either table, as there may be no reason to. If you wish to speed this up, and you haven't done so already, you can try adding one or more of the following indexes:
concerts.date
bands.popularity
concerts.bandnum
bands.bandnum
Don't just add them all, as adding too many indexes will increase database size (more tables for the indexes) and slow down update and insert queries. But try to see if adding some will help. The first two I show are to help you with the order by clause. The indexes here may let MySQL do the ordering much quicker. An index on the date field will also help here because you have the date in a where clause. The last two indexes would help the table join.
I would use the explain syntax again, and just add and drop various indexes until I find something that seems to work.
Thanks Coolsti,
I ended up adding all the ORDER BY indexed fields to the same table. It means a bit of data duplication, but things are FLYING now!
According to some of the sites I found in research, mixing multiple ORDER BY's from different tables shouldn't be done.
I was hoping somebody here might have an easier solution for me, but turns out that rebuilding my db to be more efficient only took a few hours.
Thanks,
Pete
Quote:
Originally Posted by coolsti
Hi,
we could help more if you did the following:
1) post a print out of "show create table" for your tables, so we can see what indexes you have used, what data types, etc.
2) post more Explain outputs, such as the one where you say it crashes because you change the table order in the join.
-
SELECT concerts.date, bands.name FROM concerts JOIN bands ON concerts.bandnum = bands.bandnum WHERE date>='2008-08-22' ORDER BY concerts.date, bands.popularity DESC LIMIT 0,30
Above I repeat your query. The query will not necessarilly wish to use your primary keys on either table, as there may be no reason to. If you wish to speed this up, and you haven't done so already, you can try adding one or more of the following indexes:
concerts.date
bands.popularity
concerts.bandnum
bands.bandnum
Don't just add them all, as adding too many indexes will increase database size (more tables for the indexes) and slow down update and insert queries. But try to see if adding some will help. The first two I show are to help you with the order by clause. The indexes here may let MySQL do the ordering much quicker. An index on the date field will also help here because you have the date in a where clause. The last two indexes would help the table join.
I would use the explain syntax again, and just add and drop various indexes until I find something that seems to work.
 |
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 197,024 network members.
|
