MySQL Forums
Forum List  »  Performance

Poor Performance with tables over 1Million entries
Posted by: József Rekedt-Nagy
Date: April 08, 2009 05:47PM

Hi,

I'm wondering if it is expected to have poor performance when using tables with more than 1M entries.

Have the following scenario:
Table I stores items(itemId, otherIrrelevantData)
Table D stores data(dataId,val)
Table M stores the mapping between the 2 above, let's call it DataMap(itemId,dataId). 2Indexes, 1 on (itemId,dataId) the other is on (dataId,itemId)

The relation is M-N (many to many), so any itemId can have many related data entries (estimation for avarage is 100) and a single dataId can belong to many itemIds (avarage here is higher, 10k or more).

The test database has just 1M mapping recorded in the DataMap table (M).

Q: What's the fastest/proper way to get items if the filter is matching of 3dataIds? (dataId of 1,2,3 for example)
Tried these queries so far:
1. Joining the map 3x is painfully slow: ~0.4sec
SELECT * FROM DataMap m, DataMap m2, DataMap m3
WHERE m.dataId=1 AND m2.itemId=m.itemId AND m2.dataId=2 AND m3.itemId=m2.itemId AND m3.dataId=3
LIMIT 20

2. Used OR and Group By with Having: ~0.3sec
SELECT * FROM DataMap
WHERE dataId=1 OR dataId=2 OR dataId=3
GROUP BY itemId
HAVING COUNT(dataId)=3
LIMIT 20

According to EXPLAIN the query DOES use indexes in both cases, no temporary or file sorting is done. (USING INDEX and/or USING WHERE, USING INDEX only)

Speed is UNacceptable, must stay under 0.01s with any combination of ids.
Storage Engine is INNODB for all tables btw.

am I missing something obvious here? 1M rows can't be too much for MySql, right?

Thanks for any feedback or ideas in advance, greatly appreciated.
Joe

Options: ReplyQuote




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.