Re: Limit the results of a COUNT
On Dec 31, 2007 3:05 PM, donr2020 <[EMAIL PROTECTED]> wrote: > Sorry, I didn't type the subqueries quite correctly. They all have the same > WHERE part (in this case, "WHERE Col1 = X" that essentially "joins" all the > queries. It still doesn't make sense to me. Count queries don't return anything to join. If all you want is to have them in the same result set, you can use subqueries for that, e.g. SELECT (SELECT COUNT(*) FROM x) AS x_count, (SELECT COUNT(*) FROM y) AS y_count, etc. > There are six counts that we need and we first tested it as seven separate > queries; but that took about 20% longer than one nested set of queries, as > there is a little overhead for each query. The overhead of a half-dozen queries shouldn't add up to much with an efficient client library. I think you'd be better off avoiding this complication. At the very least, I'd avoid joining things that can't be joined. > Your suggestion does help somewhat. Changing the subqueries to a count of > limited subqueries reduced a large sample query from 9 seconds down to 5 > seconds. We need to get this down some more to about 1 or 2 seconds (or less > if possible). If LIMIT helps, it probably means you either have a table scan or a subquery that runs separately for every row. Finding a way to improve the use of indexing is your best bet for making a big change. Or some kind of caching scheme. Incidentally, using separate queries would probably increase the chance of hitting the MySQL result cache for some of them. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit the results of a COUNT
Sorry, I didn't type the subqueries quite correctly. They all have the same WHERE part (in this case, "WHERE Col1 = X" that essentially "joins" all the queries. There are six counts that we need and we first tested it as seven separate queries; but that took about 20% longer than one nested set of queries, as there is a little overhead for each query. Your suggestion does help somewhat. Changing the subqueries to a count of limited subqueries reduced a large sample query from 9 seconds down to 5 seconds. We need to get this down some more to about 1 or 2 seconds (or less if possible). We're going to try using VIEW's to see if that helps. Any other thoughts would be appreciated. Best, Don Perrin Harkins wrote: > > On Dec 30, 2007 1:50 PM, donr2020 <[EMAIL PROTECTED]> wrote: >> Our search engine does a master query INNER JOINed to a series of COUNT >> (*) >> subqueries that return what the number of results would have been had the >> user chosen different "filters" (or no filter at all). As an example: > > Hmm. Why are you joining these? There's nothing to join. It looks > like these should be separate queries. > >> This query is being run against a database that currently as 100 Million >> records (and rapidly growing), and if TotCount is over about 50,000, the >> query is unacceptably slow. We need to LIMIT the subqueries to some >> maximum >> count (stop counting at, say, 50,000). Does anyone know a way to do this? > > You can use a temp table, view, or subquery to do it. For example: > > SELECT COUNT(*) FROM > (SELECT id FROM table LIMIT 5) AS limited_table > > I'm not sure this will actually be faster though. > > - Perrin > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- View this message in context: http://www.nabble.com/Limit-the-results-of-a-COUNT-tp14549988p14561532.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Tables
At 12:51 PM 12/31/2007, you wrote: Hi; Is it possible to sort tables within a given database? How? TIA, Victor Victor, You mean physically sort the table based on a field or key so you don't have to do an Order By clause each time you do a Select? Not really because the order of the table is expected to be random unless you specify an Order by clause. The only thing I can think of is to create a new table, maybe temporary or Memory table and copy the data into it already sorted. drop table if exists newtable; create newtable like oldtable; insert into newtable select * from oldtable order by col1, col2; Now you should be able to Select * from NewTable; without sorting (if you don't update it). The order should be by col1,col2. (No guarantee) If you want to sort it in order to speed it up, then run an Optimize on the table. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit the results of a COUNT
On Dec 30, 2007 1:50 PM, donr2020 <[EMAIL PROTECTED]> wrote: > Our search engine does a master query INNER JOINed to a series of COUNT (*) > subqueries that return what the number of results would have been had the > user chosen different "filters" (or no filter at all). As an example: Hmm. Why are you joining these? There's nothing to join. It looks like these should be separate queries. > This query is being run against a database that currently as 100 Million > records (and rapidly growing), and if TotCount is over about 50,000, the > query is unacceptably slow. We need to LIMIT the subqueries to some maximum > count (stop counting at, say, 50,000). Does anyone know a way to do this? You can use a temp table, view, or subquery to do it. For example: SELECT COUNT(*) FROM (SELECT id FROM table LIMIT 5) AS limited_table I'm not sure this will actually be faster though. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spawning Tables
Done. Thanks ;) On Dec 28, 2007 1:41 PM, J Trahair <[EMAIL PROTECTED]> wrote: > In my experience, having a limit of a particular number of anythings per > something, eg. components per compound word is always a mistake, whether > it's the number of payments allowed to pay an invoice, the number of > children per parent, the number of cars per family, pets per owner, etc. > There's always one that has one too many. And lots of them have only one or > two. Go for the 'mini'-table. > > Jonathan Trahair > I'm going to build a translation s/w and I'd like some advice. Many > languages have compound words. I'd like to build a table that shows the > component words. However, it depends on the compound word as to how many > components it has! Now, I could take a "safe" guess and limit it to, say, > 4 > words. But then I'm wasting a lot of storage. So I thought I'd spawn a > mini-table for each compound word. Is that the best way to handle this? > TIA, > Victor >
Mysql 4.0 Adding fields to large tables
Hello all, I am trying to add a field to a very large table. The problem is that mysql locks up when trying to do so. I even tried deleting the foreign keys on the table and it wont even let me do that, again locking up. It works for around 5 minutes or so then just either locks or the database dies and I have to restart it. Any ideas how to do this? Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
