MySQL Forums :: Performance :: Poor Performance with tables over 1Million entries


Advanced Search

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


Subject Views Written By Posted
Poor Performance with tables over 1Million entries 3590 József Rekedt-Nagy 04/08/2009 05:47PM
Re: Poor Performance with tables over 1Million entries 1924 Rick James 04/08/2009 06:26PM
Re: Poor Performance with tables over 1Million entries 1921 József Rekedt-Nagy 04/08/2009 06:40PM
Re: Poor Performance with tables over 1Million entries 1883 Rick James 04/08/2009 09:02PM
Re: Poor Performance with tables over 1Million entries 1889 József Rekedt-Nagy 04/09/2009 05:39AM
Re: Poor Performance with tables over 1Million entries 1840 Rick James 04/09/2009 06:25PM
Re: Poor Performance with tables over 1Million entries 1815 József Rekedt-Nagy 04/10/2009 01:50PM
Re: Poor Performance with tables over 1Million entries 1796 Rick James 04/10/2009 06:59PM
Re: Poor Performance with tables over 1Million entries 1838 József Rekedt-Nagy 04/11/2009 12:43AM
Re: Poor Performance with tables over 1Million entries 1824 Rick James 04/11/2009 01:55PM
Re: Poor Performance with tables over 1Million entries 1777 József Rekedt-Nagy 04/13/2009 03:07PM
Re: Poor Performance with tables over 1Million entries 1782 Aftab Khan 04/09/2009 02:52AM
Re: Poor Performance with tables over 1Million entries 1835 József Rekedt-Nagy 04/09/2009 04:27AM


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.