Mailing List
Home
MySQL General - General MySQL discussion
MaxDB - Everything about MaxDB, formerly known as SAP DB
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
Java Help - Mostly related to the MySQL Connector/J driver
ODBC - ODBC with the MySQL Connector/ODBC driver
Perl - Perl support for MySQL with DBI and DBD::mysql
MySQL++ - Programming with the C++ API to MySQL
Subjects
• mysql openssl Question
• mysql:it 's a db not a dbms how it 's possible?!
• Does the binary log enabling affect the MySQL performances?
• Strange behavior, Table Level Permission
• FULLTEXT query format question
• Preventing Duplicate Entries
• Comparing and writing out BLOBS
• Executing MySQL Commands From Within C Program
• Can 't access mysql after kernel upgrade
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Downgrade Mysql from 4 to 3 23
• MySQL Cluster Software
• mysql test 4 1 fails with the gis test
• ERROR 2002: Can 't connect to local MySQL server through socket
• Getting Identity after INSERT
• Update one field with more fields from another table
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• mysql have same function mthod as Oracle decode()
 
Search:  
Power your search with and, or, +, -, or "some phrase" operators.
Stop query on first match

Stop query on first match

2004-06-07       - By Michael Stassen

 Back
Reply:     1     2     3     4     5     6     7     8  

This doesn 't quite make sense. You seem to say that several rows will match
but then you say only one will. It must be one or the other. Perhaps I 've
misunderstood you. You also seem to imply that with BETWEEN you get a full
table scan even though there is only one match for each row. That sounds
like an indexing problem, but it is hard to say without more information.
It would help us help you if you at least posted the query and the results
of EXPLAIN. It would probably also help if you told us more about the
tables, perhaps with SHOW CREATE TABLE.

Michael

Emmanuel van der Meulen wrote:

> Hello all,
>
> I see a similar question was asked before, but it was not answered. I hope
> someone can assist me.
>
> My query uses two tables. The query selects one row on table b for each row
> on table a, but uses between in the select.
>
> It can only ever return one row from table b, for each row on table a, due
> to the contents that is stored in table b. The table contains in excess of
> a million records. What happens as a result of the between is that for the
> query, several rows seem to be candidates on table b, but once the query
> evaluates and sifts through the candidate rows on table b, only one row will
> ever match. So if I could inform MySql to stop the query for the particular
> row, once one row on table b matches the row on table a, the query would
> return hundreds of times faster. As an experiment I took one example and
> used limit and the query reduced from 4 secs to .01 sec. However, when
> doing the 'live ' query, I cannot use limit because, I do not want overall
> only 1 row returned, I want one row returned for each of the rows from table
> a which has 1 match each on table b.
>
> I 've looked in several books and searched Google but cannot get a way of
> doing this. It seems Oracle has a 'FIRST ' in their select which they use
> for such a use case. But I do not see anything for MySql anywhere.
>
> Could someone please assist me.
>
> Kind regards
> Emmanuel
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql @(protected)