MYSQL :: Wildcard Usage

Wildcard Using IN
I am not sure if this should be posted in here or in PHP, so if this is the wrong forum I apologize.

I am trying to use a wildcard inside of the IN statement in a query. From the reading I have been doing it appears that I am completely wrong in this approach, but I will still post what my problem is.

I have a record that can contain multiple options like .....

WHERE Xxx = Wildcard
I have a hard-coded select statement wherein I can change certain values.
Sometimes, I want to be able to select ALL the records. For example, suppose
I am SELECTing something with a 'username' column only I want to be able to
select ALL usernames (so I dont want to use IN or BETWEEN). is there a means
I can use to specify something along the lines of SELECT.....WHERE username
= % ? Can I use a wildcard with this somehow?

Wildcard Where
I'm trying to get all records that start with either 22 or 23. This is the last line of my query: where (mytable.myfield like '22%') or (mytable.myfield like '23%') and where mytable.myotherfield = 'B';

The problem is that I get only those records where myfield begins with 22 and no records where myfield begins with 23. (Though I know they both exist.) What am I doing wrong here?

WHERE Be Wildcard
Is it possible to search all the fields for a string of text?
Like (and I know this doesn't work):

SELECT * FROM Table WHERE * like '%pattern%';

Is there a way to do that?

Wildcard
I am trying to write an query which will return the percentage match of a wildcard search in mysql.I am using the LIKE command.

eg
(
Word in table= Barack Obama
Word passed to query= Barack
percentage match:50%(I know this percentage isn't correct)
)

Is this possible in SQL?

Wildcard
My normal search strings contain the variable 'SaleNo' such as,

search.php?SaleNo=70&Description=Silver etc......

SaleNo is a column in the database ranging from 1 through to 74 currently. Is there are a wildcard character which would resturn results from any sale number? ie

search.php?SaleNo=%&Description=Silver etc......

So the wildcard would effectively stop the SaleNo variable from being part of the query.

the sql is along the lines of:

WHERE SaleNo LIKE $varSaleNo AND Description LIKE 'varDescription'

Wildcard/s In SELECT
Is it possible to make use of wildcards in a select command?
I know that you can use a wildcard like

Select * from table
In my table I have data of the form

zoneA zoneB zoneC zoneD
low_ mass high_mass price1 price2 price3 price4
low_mass and high_mass provide a table of limits and I want to choose the price based upon the limits and the zone chosen.
Is it possible to use the wildcard (or another character) in the form?

Select zone* from table
On another note is a query of the form

select * from table where low_mass >= x and high_mass <= y
a join even though the word join is not used?

Wildcard For Tablename
Not sure if this is possible, ive used wildcards in where clauses but im looking for something for tablename.
See i have many different invoice tables, basically called
invoice_datestamp
Where datestamp is a month and a year.
I would like to do a query based upon say
invoice_%, where that would return all entries from all invoice tables instead of having to join all the tables first.
Or would the only way be to do a show tables command followed by a large join of all those tables.

Using Wildcard And Dates
This is my query:

SELECT * FROM quick_look WHERE '%-08-25' BETWEEN start_date AND end_date

% is the 4-dig year.
If I replace % with a specific year, it will return results for that year, but I have several years I'm trying to search therefore I'm using the % character. However, it does not bring me back any results (nor errors).

Join, Wildcard On One Field?
select * from table1, table2 where table1.field1 like table2.field4
Which works. However, table2.field4 can be more than a single item. It’s actually a list of items. What I need to do is this:

select * from table1, table2 where table1.field1 like ‘%table2.field4%’
I can’t figure out how to use a wildcard on a fieldname. There has to be a way. I’ve also tried fulltext but doesn’t work either, not enough text. To be clear, I'm trying to compare the fixed contents of field1 with a wildcard on the data of field4

I can't figure out how to use a wildcard in this case. When I'm passing a variable obviously no problem, but I can't figure it out when comparing one field to another.

Gettting Columns With Wildcard (%)
I need to be able to select columns from my table, the problem being that the columns are formed like this:

something_else_entirely

Where the "else" is unique, and i therefor need to make a query that takes all columns containing

"something_%_entirely"

But the solution here does not seem to work. Is it because you cant do it, or because I'm doing it wrong?

Deleting A Substring With A Wildcard?
I have a column of varchar that I would like to modify by deleting any text enclosed by parentheses. For example, if the string is "I am (not) confused" I want to return "I am confused".

I have been trying the following command:
update assignees set cleaname=replace(cleaname,' (%) '," ");

Unfortunately, this is returning zero rows changed even though I know there are plenty of instances of paren text. Do wildcards not work in this context? Is there some other way to do what I want?

Infile Import With Wildcard
I am using the following sq:

LOAD DATA INFILE 'C:Temp160.123.111.112error20061014.log' IGNORE INTO TABLE slogs.sllogs IGNORE 1 LINES;

however what I would like to do is something like

LOAD DATA INFILE 'C:Temp160.123.111.1**error*.log' IGNORE INTO TABLE slogs.sllogs IGNORE 1 LINES;

where * is ofcourse a wildcard. I understand that this cant be done in SQL but what is the best way to do this?

My situation is that I get server error logs from a web application running on a webfarm and would like to store these in mysql.

Search On Wildcard Columns
I have a query such as:

SELECT * FROM sr_main WHERE originator LIKE '%pcb%';

What I would like to be able to do is have a query where my column type is not fixed - in the example I would swap my distinct column name 'originator' with a wildcard such as '*'.

Is it possible to return all the results of a search of all fieldnames with a LIKE rather than an '='? .

Wildcard Vs Defined Selects
At which point is it better to do a wildcard select compared to defined as per below, or is it better in all cases to do a wildcard or defined?
Is there a speed difference? This is for use within php scripts and not via command line type selects.

wildcard- SELECT t.*, ................from $threads t...........
defined- SELECT t.field1, t.field2, t.field3, t.field4 ................from $threads t...........
Also...
SELECT t.*,f.name FROM $threads t, $forums f WHERE t.fid=f.fid
is this better being done with a join and which mysql version started supporting joins?

Wildcard Search Criteria
I have a field in a table that has a person's first names (e.g. Lara Michelle). I can do a wild card search on the first name but is there a way to do a wild card search on the second name.

Wildcard Column Aliasing After A Join
I'm joining several tables together, each with a good number of
columns. These columns are often named the same thing in different
tables (eg 'uid' for the primary key). I can obviously alias each
column separately:

select users.uid as userid, profiles.uid as profileid, ...

This quickly gets tedious and strikes me as a little kludgy when used
across dozens of columns.

Is there any way to, say, prefix each table's columns with that table
name so they can be easily distinguished?

Ideally there'd be something along the lines of:

select users.* as 'users_'.*, profiles.* as 'profiles_'.*, ...

which would then return

+-----------+-------------------+-----+-------------+-
| users_uid | users_othercolumn | ... | profiles_uid| ...
+-----------+-------------+-----+-----+-------------+-

RegExp Or Wildcard Applied To Cell Value?
I have a field that contains a text string that I need to match with wildcards or a Regular Expression.

The part I'm having trouble with is that the database field contains the wildcards and the input string has the full text.

For instance I could have the following SQL

Select *
from TableOddIds
where TableOddIds.IDmatch like "ABCD1234ABCD"

Where the string "ABCD1234ABCD" could be from various sources including User Input.

I would want that to match an IDmatch value of
"ABCD%ABCD"

The actual values in the DB now are more like
"ABCD CD" but I could fill in the blank spaces to make it easier (if that would make it easier)

LIKE Syntax With Database Field + Wildcard
I am having a problem with a SQL syntax here. I have 2 tables, 1 table MASTER wich contains a field ADDRESS that includes the streetname plus the housenumber.
The other table NBRHOOD only contains a field STREET that contains streetnames without a housenumber.

I would like to compare the value STREET and ADDRESS, but only part of it should be checked.

Something like this:

SELECT NBRHOOD.NAME, COUNT (MASTER.ID)
FROM NBRHOOD, MASTER
GROUP BY NBRHOOD.NAME
HAVING MASTER.ADDRESS LIKE NBRHOOD.STREET%

The last line is not correct, anyone a solution to this problem?

Check Table Wildcard Syntax?
I need to do CHECK TABLE on a lot of tables (actually on all tables in
several databases), and hoped to do something like

mysql> CHECK TABLE reports.*

which ddn't work. The only thing that seems to work is using
cut'n'paste (or some scritpting) to get a comme seperated list. Is
there really no "smart" way of doing this?

Update Using Like Wildcard And Table Column
I'm trying to update table 1 with data from table 2 where the data in table 1 is like the data in table 2. I need to use the % wildcard but I can't seem to get it to work using a column name. Is this possible, or am I going about this the wrong way. Here is the statement I have, it only updates records that match exactly.

update crowley_preowned,models set crowley_preowned.po_display_model = models.model where crowley_preowned.po_model like models.model

po_models contains say PT CRUISER LXI, models.model contains PT CRUISER. I want to update po_display_models with PT Cruiser from model for any record that starts with PT Cruiser.

Fulltext Search Using Wildcard At *beginning* Of Word?
select * from MyTable where match (MyContent) against('*my_keyword*' in boolean mode)

does NOT work... the documentations says the * wildcard can only be added at THE END of a string to truncate it, so

select * from MyTable where match (MyContent) against('my_keyword*' in boolean mode)

would work, but then searches where the keyword is something like 'off' would not return things like 'coffee' only things like 'office'...

Is there a way to create wildcards for the beginning of words?

Is There A Way To Make Mysql Accept A Wildcard Password For A User?
Is there a way to make mysql accept a wildcard password for a user?
(ie. accept any password that is given)

Unable To Get BOOLEAN Mode Working With Wildcard Matching
I have successfully been able to perform a SELECT query where if someone enters a partial word from the beginning of the word, it will match it correctly from the DB. Such as.

Appl = Apple
Telep = Telephone

SELECT * FROM knowledge WHERE MATCH (subject,question,answer1) AGAINST ('+Appl* +Telep*' IN BOOLEAN MODE) ORDER BY catageory,pkey LIMIT 0,50

This search finds all matches which contain Appl and Telep using the * in the search string.

However I am unable to create a SELECT query which is able to find words which are either the end of a word or something in between. Such as.

ppl or ephon

If I don't use BOOLEAN mode and do a simple search which is something like this
%ppl% it works fine.

/tmp Usage
are there any best practices for /tmp on a std *nix install for MySQL, and if so, what are they ? Also i can't seem to find any more information on what specifically you'd require a LARGE /tmp for in MySQL.

C# Usage
Does anyone know if C# will work with mysql??? I know it does C++, but I don't know if the connectors or whatever are out there for C#

MAX() Query Usage
I have a database of marks and I need to find out the maximum mark taken by each student in any subject. For example If am a student and I have got 60 in Language, 70 in Maths, 80 in Science and 90 in Social Studies, I would need the result "90" and the relevant Social Science subject to be displayed so that I know am good at Social Science of all the subjects.

Likewise, my friend got 65 in commerce, 70 in accountancy, 80 in economics and 90 in Applied Maths. As you may see here, the subjects are all different in both these cases. I only need to know this following result

That I am good at Social Studies and
my friend is good in Applied Maths.

The table name is marks. The fields are subject_id, mark_obtained, student_id.

When I try this query.

Select subject_id, max(mark_obtained) from marks group by student_id

In this case, I get the correct maximum mark, but the subject id displayed is incorrect. Can anyone tell me what am doing wrong here?

Getting CPU Usage Of A Query
There are some various queries running on my site that appear to use a whole lot of CPU resources under certain conditions which I haven't identified.

Is there any way I can see which queries are using up how much of the CPU instead of just being able to see the general process id for MySQL? I use cPanel and WHM to manage the server.

Index Usage
I have two tables, one is Student, which has studentid, personid, grade, fees as its columns. The other one is Person, which has personid, firstname, lastname, address as its columns. When given a studentid, I need to get the student' info, such as
lastname from the db tables.

I can do it with two queries as the following:

1. mysql> select personid FROM Student where studentid=1;
2. mysql> select lastname FROM Person where personid="the id I got above";

Or I can do it with one query:

3. mysql> select p.lastname from Student s, Person p where s.studentid=1 AND s.personid=p.personid

Both works. However, I can't tell which one is actually faster, since MySQL shows me "1 row in set (0.00 sec)" for all these three queries. Both studentid and personid are primary keys, so they are automatically indexed.

Will both indexes be used in query number 3? I saw people said that

MySQL could use in a query only one index...Does it mean only one index will be used in query number 3? I am wondering which one will give better performance.

Max(count(*)) Usage?
I have a table with a structure along the lines of

customerId
itemId
copyId

which form a primary key.

I want to know the max occurence of a particular item, based on copyId.

Ie, if 5 customers want itemId 6, 3 want copyId 1, 2 want copyId 2, should return 3 for itemId 6..

I thought something like

select itemId, max(count(*))
from items
group by itemId

might work, but alack, not the case.

Suggestions in how to do this with one query?

Counting Usage Per Day
I want to record how many times an application is used in a day. Is it possible to create a table with a date field that automatically creates a new record each day with the present date as the primary key.

Memory Usage
I've been analysing the memory usage of our server and each SQL thread seems to be taking up 10mb - since our site is basically a search site that means we're using 10mb per user which means with our 2gb RAM we can take around 120-200 simultaneous visitors.

Other than buying more RAM, what can I do to increase the number of simultaneous connections (ie - how can I reduce memory usage?) By my Calculations we need to be able to accept around 500 simultaneous searchers to meet our target.

We will also be increasing the amount of data we have - currently it is around 1.5 million records but that will be gradually increasing to around 10 million - which I'm sure will also increase the memory load on the server. Right now we have 1.4Gb Data and 380Kb overhead.

CURRENT_DATE Usage
I am trying to access some data from a database based on the date.

here is the SQL:

SELECT internallocation.LocationName, breed.BreedName, animal.DateBroughtIn from breed, animal, internallocation where (breed.ID = animal.BreedID AND internallocation.ID = animal.ShelterLocation AND animal.DateBroughtIn = 'CURRENT_DATE()')

This command returns no results... however, if I put in the date like so:

SELECT internallocation.LocationName, breed.BreedName, animal.DateBroughtIn from breed, animal, internallocation where (breed.ID = animal.BreedID AND internallocation.ID = animal.ShelterLocation AND animal.DateBroughtIn = '2005-06-24')

I get the results I am looking for. How can I use CURRENT_DATE to create a report based on the date, so the SQL does not have to be edited every time to change the day?

Memory Usage
We have Mysql 5.0 running on RH linux. We have noticed that the swap memory increases but never gets released unless we reboot the server every 2-3 weeks. How would I know whats using this swap memory?
Second question which I think maybe related to the first is that mysql is using a lot of memory when I do a top. Is there a way to limit the memory usage of mysql? I have a large table in mysql about 7 GB and I have 8 GB physical memory and swam is set to use 2GB.

MySQL 4.0.xx Admin And Usage
I've recently taken over my non-profit's Web site design, administration and maintenance with relatively little MySQL experience (although I have learned a great deal very quickly). My question is, does anyone know where I can get a manual or something like the current manual "Building your Own Website Using PHP and MySQL .."(ordered from sitepoint.com site). This manual is basing the MySQL on the most current version of 5. I'm looking for something similar that addresses the 4.0.xx(27 specifically) of MySQL

LEFT JOIN Usage HELP!
Schema:

Table1
-t1field1 --> primary key
-t1field2
-t1field3

Table2
-t2id
-t1field1 --> foreign key of t1field1
-t2field2
-t2field3
-t2field4

Database Values:
Table1
row1: 1,aaa,123
row2: 2,bbb,123
row3: 3,ccc,123
row4: 4,ddd,123
row5: 5,eee,123

Table2
row1: 1,1,aaa,123
row2: 2,1,jjj,123
row3: 3,1,aaa,123
row4: 4,2,hhh,123
row5: 5,3,hhh,123
row6: 6,2,aaa,123
row7: 7,1,jjj,123

Query:
SELECT
t1.t1field1,
t1.t1field2,
t1.t1field3,
t2.t2field2,
t2.t2field3,
t2.t2field4
FROM
Table1 AS t1
LEFT JOIN
Table2 AS t2
USING(t1field1)
WHERE
t1.t1field2 = 'aaa'

Question:
Where do I insert a "WHERE clause" if i wanted to get t2.field2 with a value of 'jjj' only? or my query is totally wrong.

High CPU Usage (99.9%) On RHEL4
i need your help on this issue. I am running a web server in my orginazation internally with mysql-4.1.12 and RHEL4. Now, its been only one day and using 'top' command its showing mysqld process using cpu 99.9% constantly. This is used by say around 4000 users.

My my.cnf file :
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

output of mysqladmin version
..........
...
Threads: 53 Questions: 324297 Slow queries: 588 Opens: 39976 Flush tables: 1 Open tables: 64 Queries per second avg: 67.788

output of mysqladmin extended-status
Variable_name | Value |
+--------------------------------+------------+
| Aborted_clients | 742 |
| Aborted_connects | 35 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 29401860 |
| Bytes_sent | 1049908921 |
| Com_admin_commands | 2352 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 40895 |
| Com_change_master ...................................................................

Calculating Table Usage
how posiible is it to calculate a table usage in byte.

Analyze Index Usage
I am running a SELECT query that is taking approx. 200+ seconds to complete. I believe I have the tables indexed properly, but apprarently not. How can I determine what indexes a query is using and evaluate the execution plan (as you would in MSSQL)?

CPU Usage Issues On A Windows Box?
As the site receives over 50 simultaneous hits, mysql uses up just about all cpu usage... I have fine tuned everything - can any one offer any suggestions or share similar experiences??

Can I Limit Ram Usage In MySQL?
I just had my server shut down because my hosting company said I was using 350 to 450 megs of ram. This seems nuts to me as I'm only running a shopping cart with about 1000 items with only about 50 visitors per day.

Since they installed mysql on the server, I asked them why they hadn't configured it to work with the parameters of their system.

Their response: "Unfortunately, there is no way to limit the RAM usage for mySQL. I apologize for the inconvenience this may cause however there is no way to do this."

Do they know what they are talking about???

Orders Table - 100% CPU Usage
I'm starting to dive into something that looks like a PHP/MySQL issue that is bogging me down (PHP5, MySQL 4.1, Apache 2 on a Windows XP ws).

(At the end of this message, the PHP code)

We operate a virtual store and there's this administrative tool, loaded by the code included, which lists orders by category (new, shipped, cancelled etc.); on the top of the page, it first shows each category's totals (3 new, 18 shipped etc.). Obviously, these data comes from an "orders" table which, as of today, has 8.103 records.

I did increase already cache and buffer sizes on PHP and that helped tremendously to load the tool *if data doesn't change* in between. When it does, the following happens:

1. CPU usage goes all the way up to 100%, and
2. it takes approx. 45-60 seconds like that until the page ends loading.

I'm no PHP programmer, but it seems to me this has to be related to the code below.

Code:

Lowering Memory Usage?
I was wondering if there's any way I can lower MySQL's = memory

useage? It often uses more than 50000kb which I consider to much.. I'm guessing I should play with my.cnf or whatever it's called (located at = C:/ and windows calls it cardnumber or something)...

Mysqld Running 100 Pct Cpu Usage
I am testing mysql 4.1 on a Windows Server 2003 with a website running on Internet Information Services. I installed mysqld as a service.

As times goes by, mysqld begins to reach 100 pct cpu usage and doesn't goes below that, Windows usage is slow, folders opens slow, navigating locally turns to be slow, and sometimes it makes the server to freeze or restart.

When this begins to happen, I executed the command "Show processlist" and I got the following result. If somebody can point me where is my mistake or where is the problem or any solutions for this, I'm still in a learning process for mysql. Code:

Key_index_size Setting And Usage
I am using MySQL 4.1.12, compiled from source, on a Linux 2.6.11.10 kernel form the kernel archive. My Hardware is a Dual Xeon 2.8Ghz with 8GB of RAM and U320 SCSI disks. The database I am serving is about 6GB total, with the bulk of the data in 2 big tables, about 1.5 GB each.

The index files for these two tables are 1.4 GB and 415MB. In my /etc/my.cnf file I have the key_index_size set to 3GB, but the most that will get used is 1.49GB, either after doing an "Optimize tables" or an "INSERT INDEX INTO CACHE" statement.

My question is: How does MySQL decide how much of the systems memory to use for the key_index_size and any other of the processes allocatable address space. Also, under linux is the allocatable address space to an individual process 3GB or 4GB?

Allowable MySQL CPU Usage
I am running this query (with subqueries) in MySQL under Windows:
SELECT sum(quantity)/(SELECT sum(quantity) from orders), partID from orders group by partID

I noticed that it runs very slowly. It appears that MySQL is only using about 2% of CPU capacity, for some reason. The query takes almost 20 minutes to run on my table (whereas the same query takes only 1 minute in MS Access!).

Is there a way to allow MySQL to use more of the CPU, so these queries can run more quickly?