How to Query by First Part of a VARCHAR?
If I have a table with rows like this, all varchar: DOG_LUCY DOG_CHARLIE DOG_LASSIE DOG_XRAY CAT_MR_BIGGLESWORTH CAT_SCRATCHER CAT_WHISTLER what is the form of a query that will return the rows where the first part of the string matches? For example, what if I'd like to return the rows that begin with CAT_, which should give 3 rows? Thanks. P.S.--This example is contrived to illustrate what I'm trying to achieve. My actual application is different. A database of cats and dogs would naturally be best structured differently. Thanks.
Re: How to Query by First Part of a VARCHAR?
SELECT column FROM table WHERE column LIKE CAT\_%; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Query by First Part of a VARCHAR?
On 7/4/07, gary [EMAIL PROTECTED] wrote: SELECT column FROM table WHERE column LIKE CAT\_%; Would it be reasonable to assume that if column is indexed, the query would execute quickly, i.e. I would assume that the indexing would facilitate this kind of query?
Re: How to Query by First Part of a VARCHAR?
In the last episode (Jul 04), David T. Ashley said: On 7/4/07, gary [EMAIL PROTECTED] wrote: SELECT column FROM table WHERE column LIKE CAT\_%; Would it be reasonable to assume that if column is indexed, the query would execute quickly, i.e. I would assume that the indexing would facilitate this kind of query? Yes, but only for prefix checks like in this example. ` LIKE %CAT% ' or ` LIKE %CAT ' can't use an index. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to Query by First Part of a VARCHAR?
On 7/4/07, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Jul 04), David T. Ashley said: On 7/4/07, gary [EMAIL PROTECTED] wrote: SELECT column FROM table WHERE column LIKE CAT\_%; Would it be reasonable to assume that if column is indexed, the query would execute quickly, i.e. I would assume that the indexing would facilitate this kind of query? Yes, but only for prefix checks like in this example. ` LIKE %CAT% ' or ` LIKE %CAT ' can't use an index. Thanks. I was able to confirm the behavior by creating a table with three identical varchars, populating them randomly with a string of 6 digits but setting each varchar within a row the same, and executing queries. s3 is indexed (below). LIKE CAT% was obscenely fast on an indexed column. LIKE %CAT% was obscenely slow. Thanks for the help. - mysql explain stest; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | a | bigint(20) | | PRI | 0 | | | s1| varchar(200) | YES | | NULL| | | s2| varchar(200) | YES | | NULL| | | s3| varchar(200) | YES | MUL | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) mysql select count(*) from stest; +--+ | count(*) | +--+ | 1480131 | +--+ 1 row in set (0.00 sec) mysql select * from stest where s1=123455; +++++ | a | s1 | s2 | s3 | +++++ | 246823 | 123455 | 123455 | 123455 | +++++ 1 row in set (2.16 sec) mysql select * from stest where s3=123455; +++++ | a | s1 | s2 | s3 | +++++ | 246823 | 123455 | 123455 | 123455 | +++++ 1 row in set (0.00 sec) mysql select count(*) from stest where s1 like %; +--+ | count(*) | +--+ | 136 | +--+ 1 row in set (2.10 sec) mysql select count(*) from stest where s3 like %; +--+ | count(*) | +--+ | 136 | +--+ 1 row in set (0.00 sec) mysql select count(*) from stest where s1 like %000%; +--+ | count(*) | +--+ | 5585 | +--+ 1 row in set (2.19 sec) mysql select count(*) from stest where s3 like %000%; +--+ | count(*) | +--+ | 5585 | +--+ 1 row in set (2.78 sec)
Re: How to Query by First Part of a VARCHAR?
the thing to remember is that if you only want strings that start with CAT you'd never want to query with %CAT% because this could match DOG_CATHY. % is a wildcard that matches any number of characters including none. if you want to match a single character you use _ if you actually need to search for _ or % you can escape them with a backslash \% or \_ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
