MYSQL :: Query Performance On Column Conditions

Need Some Help In Improving Performance On A Query (was: Hi People)
I need some help in improving performance on a query which is taking too long to execute.

I have 3 tables:
table 'photo' stores information on photos and it has 2 important fields:
- photo_id
- title

table 'item' stores information on generic items in the system (photos is an example). It has 3 important fields:
- item_id
- item_type (like 'photos')
- submitter_id

table 'user' stores information on users in the system. It has 2 important fields:
- user_id
- name

I would like to get the number of photos that either have a title that contains 'a' or the submitter's name contains 'a'

I tried the following:

SELECT count(DISTINCT photo.photo_id) NumItems
FROM (photo, item, user)
WHERE (item.item_id = photo.photo_id AND
item.item_type = 'photos' AND
(photo.title LIKE '%a%' OR (user.name LIKE '%a%' AND user.user_id = item.submitter_id)));
This query is taking way too long (sometimes up to 10 seconds
Note that I have over 2,000,000 records in the 'user' table (I suspect the problem is there)
I only have 2 recods in the 'photo' table
I only have 2 records in the 'item' table

String Replacement Query - Performance?
I was lying in bed last night thinking about this type of string replacement query:

UPDATE table SET field = REPLACE ( field, 'old_str', 'new_str');

and wondering how intense it is.

How does mysql tackle running a query like this?
Is there any way of improving the performance?
I know you can create on index on a text field, but only the first 1000 chars, and would it even help?
Does a table twice as big take twice as long?

MySQL General Query Log And Performance
I'm wondering how big impact on performance would have turning on the general query log. I read in manual it should be turned off in production environment to increase the speed but I didn't find any information about performance (how much cpu/disk usage it would take). Is it like 1% performance lost or is it bigger?

Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results.

Environment
Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram

mysql 4.1 with odbc 3.51 MYISAM
windows 2003 server std edition
date, account and invoice number are indexed
Database size 18 million rows

I am querying (selecting) columns of a date and an account

our tester program that opens a socket to the Mysql database and does a select for the above n times
each time the date and the account is randomized to minimize hits on records closeby.
This program will perform over 1000 queries per second.
At the end, the Mysql socket will be closed
Even when the socket is closed each time, I still get 400 queries per second.

When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results.

Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.

Where Conditions
I am using MySQL phpmyadmin on my server. I would like some examples of different conditional codes to retrieve info from my tables.

LIKE Conditions
Basically I have a table, with a column which holds a list of related tags separated by spaces. The user can search for a row using a key word that will search this column (and the column named header too) and return if the keyword appears in the tags (or heading)... I tried to achieve this by using the following SQL but it actually returns every entry in the database... I have never used "LIKE" before so if someone could give me a slap and say I am doing something stupid that would be awesome .

SELECT * FROM VIDEOS WHERE VIDEO_HEADER LIKE '%$seachText%' OR VIDEO_TAGS LIKE '%$searchText%'

WHERE Conditions
I'm trying to optimize a search and I'm wondering if anybody knew whether the order of the conditions in the WHERE clause actually matters. Is it parsed from left to right? If so, then should I put the conditions likely to eliminate the most records first, followed by those that will eliminate the least?
Also - I need to join several tables for this search (1 master table that contains user info and about 7 secondary tables that include additional information on users) and I am trying to decide whether I should do the joins one at a time and eliminate users in a series of steps (to prevent getting an overly large table), or if I should evaluate and combine everything in a single statement. The problem is I don't really know what the parsing order of the SELECT statement is.

2 Of 5 Conditions Match
I need my query to match at least 2 of 5 given conditions.

Example:
I have conditions
content REGEXP 'A'
content REGEXP 'B'
content REGEXP 'C'
content REGEXP 'D'
content REGEXP 'E'

I need database to return this entry only if at least 2 conditons are true.

Need Help With Multiple WHERE Conditions...
i'm wondering if anyone knows how to have multiple WHERE conditions...i'm using php/mysql as my programing languages...

example --> SELECT * FROM table WHERE rid=$ride && uid=$userid && accepted=Ɔ'

I need help selecting rows where all three of the conditions above are met...anyone knows how to do this?

MySQL 5.0 And OR Conditions
I have heard that version 5.0 of MySQL has performance gains for "OR" conditions. Can anyone confirm this? If so, how significant are the performance gains over 4.1?

For instance, I am referring to something like: SELECT * FROM some_table WHERE this_thing = 'something' OR that_thing = 'whatever';

Search On Several Conditions
I want to retreive data from a table on several conditions in order; for example;

All i am trying to do is select all records on the following condition;

User_id = variable AND (Profile = "variable" OR Profile = "Both") ORDER by hits in DESC

I came up with this statement but no matter what i do i cannot get it to produce the result above

"SELECT * FROM links WHERE user_id='$user_id' AND (profile='$mycurrentprofile' OR profile='Both') GROUP BY total_hits DESC"

Can anyone offer me any help please, would really appreciate this as its doing my head in.

BTW, the following statement seems to work so not sure whats going on ? Only thing i can think is that its screwing up if total_hits above is equal 0 ?

"SELECT * FROM links WHERE folder_id='$activefolder' AND (profile='$mycurrentprofile' OR profile='Both') GROUP BY title ASC"

Sums For Two Different Conditions
I want to combine the following 2 queries into 1 query where it would give me 2 results (proflad and proftot) but don't know how

select sum(return-inv)as proflad
from allbets where com="lad"

select sum(return-inv)as proftot
from allbets where com="tot"

Transactions And Race Conditions
In an application I'm developing, I'm at a point where there needs to be tight integration of application-level logic sprinkled in between several consecutive SQL statements. This negates the option to use stored procedures since subsequent SQL statements depend on application logic that cannot be transferred into the database. I need the ability to rollback on errors (i.e. I need to use a transaction), but I also need to avoid race conditions.

Reading the MySQL documentation, START TRANSACTION will implicitly issue an UNLOCK TABLES statement; conversely, LOCK TABLES will implicitly COMMIT any active transaction. At this point it appears that I have to choose between transactions and locks, but I need the functionality of both.

Left Join Conditions
Two tables.

table1, table2

Table 1 contains all unique items.
Table 2 contains x references to table 1, unique by customerId

I want to do a join onto table2 where table1.id = table2.table1Id AND table2.customerId = the user's ID

I can't quite figure out how to work in the idea of an AND into my left join. Unless both those conditions are two, I don't want any data from table2.

Only solution I can think of so far is to just left join on the id's, but select the customerId from table2, and as I loop through, check to see if it's supplied and the right id, and keep track of duplicate items from multiple customers myself.

Suggestions?

Conditions On Left Join
I'm having trouble constructing this left join query. I want to retrieve all rows from tProducts, and relevant matches from pictures, however what I have got just jams up the server and I have to restart. Am I doing it wrong?

SELECT prodCode, prodName
FROM tProducts LEFT JOIN pictures
ON productID = relProdId
AND prodCode = 'SF2'

Select With Multiple Conditions
I've a table called tag_log.

In this table are two columns called tag_id and sub_id. This table is used to link tag names to stories.

I'm trying to write the query for a search. The search is looking for all the sub_id that matches the tag_id given.

Example of what I have currently:
select distinct(sub_id) from tag_log where tag_id in(101, 102, 103);

In the above example, it was performing a union instead of what I really want. It was returning results that have either one or all of the tags.

What's required is for the story to have ALL the three tags. It's should be like a typical search, where the more search terms you enter, the more focused the results will be.

Conditions In UPDATE Statement
i have some data to be updated in a specific row - WHERE id=1

data:
$min_time, $max_time, $time, $attempts

so, i would like to calculate new avg and replace min_time with new value, if new min_time is smaller then that one stored in the database.

"UPDATE mytable SET
avg=(avg*attempts+$time) DEV (attempts+1),
min_time=**here i could use help** ??IF(min_time>$min_time)$min_time ELSE min_time??
**and similar with max_time .

OUTER JOIN With Extra Conditions?
I need to get a list of products in a certain category, along with the quantity of each item already added to the shopping cart for a given session number. I'm having a heck of a time satisfying the latter condition.

shoppingCart table:
+--------+-----------+-----------+-----+
| cartID | sessionID | productID | qty |
+--------+-----------+-----------+-----+
product table:
+-----------+--------------+------+--------+
| ProductID | ProductCatID | name | Active |
+-----------+--------------+------+--------+
Here's my base query, which just gets all the products in a given category.

SELECT product.*
FROM product
WHERE `ProductCatID`='{$id}'
AND `Active`=Ƈ'
Here's the query I've got so far:

SELECT product.*, shoppingCart.qty
FROM product
LEFT OUTER JOIN shoppingCart ON shoppingCart.productID = product.ProductID
WHERE `ProductCatID` = Ɗ'
AND shoppingCart.sessionID = '{$sessionID}'
AND `Active` = Ƈ'
Obviously, this does NOT work, because it limits the query to ONLY products that have the specified sessionID. I need it to return ALL the products in the category, but give me the quanity for items in the shoppingCart table, ONLY IF the sessionID matches (otherwise it should return NULL)!

Combining UPDATE Statements With Different Fields And Conditions
I'm slightly paranoid that I haven't unearthed an existing answer to this question, but the ones I have been able to find didn't have two different variables requiring two different conditions. Basically, I want to know if it's possible to combine the following UPDATE statements into a single query:

UPDATE table SET field1 = field1 - n WHERE field 1 > x
UPDATE table SET field2 = field2 - n WHERE field 2 > x

.......

How Do I Write Multiple Conditions In An MySQL IF Statement?
I am using version 3.23 and want to write something like this:

Granting User Access On Field With Restrictions Or Conditions
I am planning to develop a database that will have many users. Assume I have a table called Customer having these fields (id, name, user). Assume we have these Data:

ID Name User
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1
4 Cust4 user2
5 Cust5 user2

I want the users to have access on those records that are created by them only or those that have their name on in the (User Field).

For example, if (user1) accessed the database and called: "SELECT * FROM customer;" it should return these data only automatically (or throw an exception at least):

Result:
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1

(or throw an exception at least) that he cant call this statements without a where cluse like (USER=user1).

also, the user should have only the right to insert records having their name on in the (User) Field. For example: user1 can only call : "INSERT in CUSTOMER Values (6 , Cust6, user1), if he inserts user2 instead, the system should through an exception.

I hope I made clear examples of what I would like to build. How can I acheive this?

Column Name Query
suppose that we have a dB has 200 tables .
how can i find out which ones have column name is username ?
what is requirement query ?

Getting Sum Of Column Within A Joint Query
Wondering if this is at all possible - For now, I am running two query.

(1) First query searches for all records in table A
(2) Using a while loop to display data grid
(3) Within loop, running a 2nd query against table B to get the SUM of two columns ....

Query Result As Column Name?
I now have made a view with results of several years for all the people in the database.

I can query the view to get the three winners, sorted decending on Winstperc. The result looks like this:

Seizoen    Naam        Winstperc
2006/07    Jim        1,0909
2006/07    Bill        1,0896
2006/07    William    1,0879

And that's exactly what I want to know but I need it like this:

Seizoen    Nr1        Nr2        Nr3
2006/07    Jim        Bill        William

Looking through the threads I got some nice ideas but...
I tried it with IF and with Case but never succeeded.

So, I think I'm overlooking something. Please show me the way?

Query A Column Into Fields In A Row
I'd like to query a table and pull a column from (n) rows, and place that in a new table as (n) fields in a single row.

Is this possible?

like this ....

Query By Column Comparison
I'm trying to pull a row by comparing that the numerical value of one column is greater than the numerical value of other columns. The manual is quite confusing as to how to phrase such a thing, and I haven't done much more than simple queries up until now.
MySQL version: 4.0.22

What I started with that doesn't work:

select * from someTable where col1 > col2 and col1 > col3 and col1 > col4 order by someThing limit 1

I'm basically trying to grab one row where the value of one column is greater than the values of the remaining similar columns.

Display Result From Query In More Then 1 Column
I want to make a query to a database, and display it on my php page either through php code or with 2 queries to my mysql database.

This is how i want it displayed:

Data 1 Data 2
Data 3 Data 4
Data 5 Data 6
Data 7 Data 8
... ....
Data X Data X

This is my current code which i use and get it displayed in 1 row:

Creating A Rank Column For Query
My problem is how do I create a rank column for a query. Meaning depending on a students score they will have a rank of 4 for example. Also if two students ahve the same score there rank should be the same and the next students rank would be 2 more. I tried using a global variable but dont know how to increment it properly. There must be an easier way of doin this then Im thinking of.....

How To Query For A Column With White Spaces Only?
A column with varchar type and NULL allowed. How do I write a sql so that I can retrieve the records with unknown number of white spaces only?

Running Query For Column Names
Is there a way to run a query just to see what the columns are in a database?

Column Names On Top Of The Query Output
Is it possible to get the columnnames on the first row of a query output? Is there a "flag" I have to add?

Distinct Column In Multi-Table Query
I want one article from each 'sub topic'. Each sub topic is given a 'sub_id'.

PHP

$garticle = mysql_query("SELECT DISTINCT(feeds.sub_id),
articles.title, articles.feed_id, articles.abstract,
articles.link, articles.date, site.articles,
$formula AS importance
FROM articles,feeds,site
WHERE articles.date > DATE_SUB( now() , INTERVAL 1 day )
AND articles.feed_id=feeds.feed_id
AND feeds.site_id = site.site_id
ORDER BY articles.article_id DESC LIMIT 16", $connection)
or die(mysql_error());

The article has a 'feedIid', this JOINS the feeds table. The feeds have a 'sub_id'. I only want the 16 most recent articles from DISTINCT 'sub_id's

A Query To Select A Column When A Percentage Of Values Non Zero?
I wounder whether some of the experts out there might be able to help
me with a problem I'm having. I do not know whether this is possible or
not...

I have a large table of stock price data which is straight-forward
enought. I can select prices based on a ticker and date ranges.
However, what I'd like to do is to select prices only when, say 75% of
them are non-zero (with the goal of eliminating new/suspended/delisted
stocks).

Of course I could just select where price > 0, but then I might get
only a few rows where this is the case. What I would like to do is
always get the full date range of prices, but only if >75% are there.

How Do I Test If A Value Exists In A Table Column With A Query?
I know this is probably something that should be obvious or easy, but I can't figure out how to test whether a value exists or not.

For example, in table "bookgroup" in the "meetingdate" column (which contains dates for meetings), how could I test whether the date "20040307" exists? I don't want to return a value in the same row... I simply want to return a boolean TRUE or FALSE value... like 1 if it does exist, and 0 if it doesn't. Can I do this without creating another column specifically for this purpose? Is there a query which can do this?

All I have so far is

FROM `bookgroup`
WHERE meetingdate='20040307'

but I don't know where to go from there.

Unknown Column Error With Left Join Query
I can't get my left join operation working whenever my on clause includes a comparison to another table's column. I keep getting errors like this:
#1054 - Unknown column 'pr.risk' in 'on clause'

The query I am trying to execute is shown below. All was working well until I put that top join in. And yes there are risk and issue columns in the pr table....

Select Query On Latin1 Or Utf8 Column: Which Is Faster?
Assume you have two varchar (or Text) columns named L and U which are
identical except that the charset for L is latin1 and the charset for
U is utf8. All the records in L and U are identical in terms of
content, consisting of only 7 bit ASCII characters. Both columns have
indexes of the same type (e.g. assume Unique indexes if you want).

Here's my question: Will the fact that column U has a utf8 charset
make select queries run slower on that column? For example, will the
query

Select * from table where U='blahblah'

run slower than the query

Select * from table where L='blahblah'

??

Significantly slower?

I'm thinking that a query on the latin1 column would go faster since
the program knows upfront that one byte equals one character, and
vice-versa; whereas in the same query on a utf8 column the program has
a lot more "overhead" because it has to constantly be determining how
many bytes represent a character. Since queries on string columns are
case insensitive, the program can't just do a byte-for-byte
comparision; rather, it has to compare *characters*, and sometimes
convert a character from upper to lower case, or vice versa, in order
to do the case-insensitive comparison.

The actual column in question is going to store URLs, so it should
only need to hold 7 bit ascii characters (in theory at least). So, in
terms of content, it shouldn't matter whether I make the column latin1
or utf8. But in terms of query speed....on, say, a few million
records...??

I would like to do everything in utf8 (web pages, forms, mysql
database columns, etc.). But since that one column might be heavily
queried, maybe I should make an exception and do it in latin1?? I wish
the mysql docs would speak to these issues....

Specify Conditions For The Left Side Of A Multiple Left Join
I'm doing a left join that looks like this standard example:

SELECT t1.c1, t2.c2, SUM(t3.c3)
FROM t1
LEFT JOIN t2 ON t1.c1=t2.c1
LEFT JOIN t3 ON t1.c1=t3.c1
GROUP BY t1.c1

The problem is that I also want to specify a condition for selecting records from t1:

WHERE t1.c1='x'

so that only the records with that value in c1 will be returned on the left side of the join. I don't know where to put this in the SQL.

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's
the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift
and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order.

now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it
does work.
Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there
is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

Performance Of LIKE
I'm weighing the performance of materialised path vs. nested set algorithms, for storing a tree structure. The tree will have a lot of searches, a fair amount of inserts, but probably fairly few move-operations. The decisive factor seems to be the speed of a LIKE-query. I know, that regular expressions perform pretty well, as long as they are anchored. From this, I deduct, that LIKE must be able to perform equally good, since a path is indeed anchored. The questions is, if a LIKE will always result in a full scan, or if it can utilise indices? If it doesn't allow use of indices, would it be worth it, to use an equality comparison, on a substring (Which is essentially, what I'll be querying for) or am I just entirely missing the point?

Performance
I'm developing a PHP script and using MySQL.

What is better, have one database with many tables por all accounts (example: account1_table1, account1_table2... account2_table1....) or one database for each account?

Is there many differences in performance?

Which Is Better In Performance
country

(id) country
(1) America
(2) Germany
(3) Canada
(4) China
(5) Egypt
(6) Russia
(7) Korea
(8) France
(9) Brazil

city

(id) city
(1) Berlin
(2) New York
(3) Cairo
(4) Paris
(5) Seoul
(6) Peking
(7) Chicago
(8) Moscow
(9) Denver
I have two tables like the above.

And I have one of these tables below.

message1

(id) (city) message
(1) (2) I love New York.
(2) (5) Kumkang mountains are beautiful.
(3) (4) I was born in France.
(4) (6) Great Wall is great.
(5) (7) Chicago is a big city.

message2

(id) (city) (country) message
(1) (2) (1) I love New York.
(2) (5) (7) Kumkang mountains are beautiful.
(3) (4) (8) I was born in France.
(4) (6) (4) Great Wall is great.
(5) (7) (1) Chicago is a big city.

code1

select city, country
from message1
left join city on city.id=message1.city
left join country on country.id=city.country

code2

select city, country
from message2
left join city on city.id=message2.city
left join ccountry on country.id=message2.country
I think The codes above have a same result.(sorry, I didn't test it.)

Performance
I have a "performance" related question.

I am developing an events calendar with php and mysql. In the calendar I want any day that has events to be a link. Therefore just before a day is added, I want to query the database to see if any events occur on that date.

Is it bad practice to query the database that much? In this case it'll be hit 30 or 31 times. also, what takes up less space in the db... the mysql date data type or the php timestamp?

Performance
I have 3 tables: books, images and books_xref_images (with the xref
table merely matching up bookIDs and imageIDs.

Assuming I only want information on one book (and it's image) would
it be quicker to do 3 LEFT JOINs in one query....

.... or 2 distinct queries, with the first retreving the book info and the
second getting the image info?

Performance
One of my customers runs MySQL. They encounter performance problems. It looks to be the storage, although they run on performant EMC storage (15000 rpm disks, RAID-1). I asked them how their MySQL is setup and they run MyISAM for nearly all tables, altough their application is typical OLTP. It is in my knowledge that you should use InnoDB for OLTP databases, or am I wrong.

My customer told me that they did tests with MyISAM vs InnoDB and he told me MyISAM was more performant. Is that possible ? I can imagine that they never will have enough server memory to keep all tables of their 50GB database in memory.

If there is not enough server memory to keep all tables in memory via MyISAM, what will happen ? Swapping, resulting in loads of extra I/O ?

Best Performance For WHERE LIKE AND ..
I'm doing heavy searches on a table with statements like

SELECT * FROM thetable WHERE col1 LIKE '%ab%' AND col1 LIKE '%cd%'

.. and so on. I need to search for keywords shorter than 4 characters, so I can't use fulltext MATCH-AGAINST. When searching using WHERE-LIKE -method above, should I use FULLTEXT or INDEX in the column in question?