Should I Use MySQL Query Cache?

MySQL Query Cache: Should I Cache Small, Simple Queries Or Only Complex Ones?
Query cache works great for long, complex queries, but should I also be caching the simplest of select queries.

For example let's say I had a table with 100 records and I needed to select something using a unique key:

SELECT name FROM products WHERE id = 3
Is caching the above pointless, especially in terms of wasted cache memory, considering how basic it is?

---

MySQL Query Cache
Is there a way to save or export the query cache in MySQL? I would like to keep the queries to run again when I change the data in my tables.

---

Query Cache Not Working: MySQL 5 / Windows XP
I just installed MySQL 5, and its running great except that the query cache is not working, despite the configuration excerpt from my "my.ini"
below:

query_cache_size=5M
query_cache_limit=2M
query_cache_min_res_unit=4096k
query_cache_type=1

From the MySQL command line, if I enter: SHOW VARIABLES LIKE
'have_query_cache';

I get:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+

Also, doing a: SHOW STATUS LIKE 'Qcache%'; gives me:

+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 5234168 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 450 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+


I figure the "Qcache_free_blocks" and "Qcache_total_blocks" values of 1
are creating the problem; it's basically telling me that there are no
(okay, 1) free blocks to put my query. However, these values never
change.

The query cache is not vital to me, but it does improve performance, and
I'd like to get it going....

---

Query Cache
I have some problems with i suppose is the "Query Cache".
I have mysql server version 5 installed.

I have an applicattion with works with odbc 3.51 driver.

The problem is... the effect of query cache when i work with 2 or 3 concurrent sessions in my application.

I'm doing this with a session (session 1):

select * from table...
update table...

In the other session (session 2) i do the SAME select:

select * from table...

I can't see at session 2 the changes of sentence update of the 1st session!! Only i can do this it's work the session and begin.

I supossed this a query cache, i dessactivate it i have tried. Know i have:

query_cache_type = OFF
query_cache_size = 0

---

Query Cache
I was playing around with the query cache. However, after I set global query_cache_size = 20480; and show variables like 'query_cache_size'; the size shown is still 0. Also tried to change the my.ini file. but still 0. The query_cache_type is set to 1.

---

Query Cache Help
I have a VB front end that works against a MySQL backend.  There is a search module that displays matching records and allows you to edit and delete records.  If records are deleted and the user than does another search, the results still display the deleted records even though they are no longer in the table.  I am assuming this is a cache issue.  I don't want to eliminate the cache in fear of performance degrade but would like to give the user the option to refresh which would clear the query cache.  Has anyone done this before or does anyone have a better solution?

---

Query Cache?
So I'm fairly new to mySQL. I've developed a site in php, and I was wondering how I could speed up the queries. Essentially, every time a page is loaded, it has to run through a database of around 2,500 rows, and it has a bit of a lag every time that occurs. Is there any way to cache the database to speed up performance after the initial load?

---

Query Cache
Is there any way to see which queries are cached?

---

Query Cache Adjustments
The query cache is brilliant (in theory), but I find in practice it rarely does much.

The problem is that the cache is flushed after every table insert/update. The benefits of this are obvious (keep query results up to date), but I feel having the ability to adjust how often the cache is updated would be immeasurably beneficial.

If only I could have it updated every 5/10/50 inserts/updates (depending on level of table access).

This could lead to a boon to performance with little problem of data getting stale.

Is there anyway way to do this?

---

Query Cache - What Gets Pruned?
When the Query Cache is full and something has to be pruned, how is the decision to prune a query made? Is it first in-first out, or least recently used-first out? Can I manually set a priorty on what is removed from the cache first? Is there a "Never Remove From Cache" flag?

---

Query Cache Issues
I have a MySQL server running 4.0.12 on a 12 CPU Sun U4500 with 12GB of
memory.

With the query cache running, this machine would noticeably "hiccup"
(just stop responding to requests) every so often and wouldn't squeeze
more then 1000 queries per second or so as a result (heavily mixed OLTP
work, with a lot of SELECT's and almost as many UPDATE's).

Without the Query Cache (and no other tweaking), I'm now at over 2100
at the same time of day.

Is there a problem with the query cache that causes MySQL to be far
less scalable then just straight MySQL/InnoDB?

Are there any thoughts on this? Is there a resource specifically for
high performance/high load MySQL implementation/usage?

---

Understanding Query Cache
when using the search on one of our sites, it takes 4-5 seconds to return a result but if I immediately search for the same thing again it returns instantly due to the Query cache which is great. What seems strange is that 15-30 seconds later it ignores the cache and runs the same query again for a result. Is that normal? It seems like the Query Cache should maintain the query and result until the query cache runs out of memory and starts dumping the oldest records.

---

Query Cache Issues
I have a MySQL server running 4.0.12 on a 12 CPU Sun U4500 with 12GB of
memory.

With the query cache running, this machine would noticeably "hiccup"
(just stop responding to requests) every so often and wouldn't squeeze
more then 1000 queries per second or so as a result (heavily mixed OLTP
work, with a lot of SELECT's and almost as many UPDATE's).

Without the Query Cache (and no other tweaking), I'm now at over 2100
at the same time of day.

Is there a problem with the query cache that causes MySQL to be far
less scalable then just straight MySQL/InnoDB?

Are there any thoughts on this? Is there a resource specifically for
high performance/high load MySQL implementation/usage?

---

Redirecting Query Cache
I am trying to find out how to redirect the mysql query cache to a hard drive.  

My goal is to make it usefull again after a restart.

---

ON DUPLICATE KEY UPDATE And Query Cache
After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me.

When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ...
statemtent, it seemed like the update was not taking place.
After viewing the webpage in question, (and doing the manual select statement), the old data was being returned.

I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned.

Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates.

What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.

---

Query Cache: Simple Question
I just have a simple question about MySQL QQ.

What I want to know is: Is the query cache only setup to apply to queries that stay the same?

PHP

"SELECT one,two,three FROM table LIMIT 1"

(Standard query, no WHERE clause, works the same for all visitors)

But a Query like this then doesn't stay the same

PHP

"SELECT one,two,three FROM table WHERE id='".$id."' LIMIT 1"

Since the ID value depends on a php variable, thus is different each time, then the query is in fact not the same.

Is the use of the query cache still beneficial in a case like this?

---

Weird Query Cache Numbers
I'm running 4.0.18, and the queries in cache variable grew from about 2K
to 30K in a matter of hours when we hardly used the database at all.
Has anyone seen something similar? (The cache size was set to 100M and
cache limit to 5M -

---

Benchmark Command And Query Cache
I'm trying to optimize my database, and have read about the Benchmark command.

Here is one that I am playing with:
SELECT BENCHMARK(100,'SELECT COUNT(*) FROM table');

I assume this syntax is correct because I don't get any errors. However, this also doesn't return any errors:
SELECT BENCHMARK(100,'Any Invalid SQL Statement');

Also, I am having a hard time understanding how BENCHMARK can be effective regardless of syntax. I'm guessing the first time a query is ran, it will be stored in the QUERY_CACHE. Subsequent iterations of the benchmark command would basically be pointless (because the query is cached and not actually ran against the DB).

Thoughts?

---

Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys.

The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again.

for example:
select * from table where id=2 order by index_field desc; 4/5 seconds.
select * from table where id=2 order by index_field desc; 0.02 seconds.

select * from table where id=3 order by index_field desc; 4/5 seconds.
select * from table where id=3 order by index_field desc; 0.02 seconds.

etc, etc...

---

Skip-new Disables Query Cache?
skip-new is explained as follows in the manual: "Don't use new,
possibly wrong routines." (4.1.1 mysqld Command-line Options). Is
there a list of the routines that will be disabled when using this
command-line option?

The manual also states the following: "concurrent_inserts If ON (the
default), MySQL will allow you to use INSERT on MyISAM tables at the
same time as you run SELECT queries on them. You can turn this option
off by starting mysqld with --safe or --skip-new." (4.5.7.4 SHOW
VARIABLES).

So when i was trying to turn off concurrent insterts I used -skip-new
which also disabled query caching. It took me a little while to figure
this out. I'm using skip-concurrent-insert now. Should this section of
the manual be updated?

System Info:
Windows 2000 SP3
mysqld-nt.exe Ver 4.0.13-max-debug for Win

---

The Query Cache Does Not Return Stale
I am reading up on query cache feature and would like to have the following question clarified.

From mysql 5.0 manual, section 5.14, it says "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed."

I would like to know what is the definition of "when tables are modified".

does it mean?

1. new records inserted into table?
2. records gets updated?
3. table structure is modified by using alter table command?

---

MySQL Cache
I'm developing a Java application using MySQL v.3.23
via JDBC. When I change something in the database
from one computer, I cannot see the modification from
different computer. Even if I exit the application
changing the data, the second application is still
unable to see the changes.

---

Does Mysql Cache Joins?
will mysql still cache the first... umm... "quert" where it's selecting the forum name in this queru

SELECT
forums.forumname,
threads.threadname
FROM
forums, threads
WHERE
forums.forumid = 1
AND
threads.threadid = 7

or would it be better doing two queries, and knowing it'll cache the forums one?

SELECT
forum_name
FROM
forums
WHERE
forumid = 1
^^ Will get cached

SELECT
topic
FROM
threads
WHERE
threadid = 7
^^ might get cached after a few times the query has been called?

I'm quite new to "query cache"
I didn't know mysql did this
and now I'm trying to improve some of my apps, so I thought I'd askl =]

---

Does Mysql Cache Results?
I'm doing some web development and have mysql (3.23.x) installed
to test with (win2k, but I don't think this question is particularly
platform specific).

I have a reasonably complex query which I am trying to optimise.
When I first startup mysql, and perform the query, it can take
10 or so seconds. However, further subsequent queries (sorting
by the same column) return in a fraction of a second. This is true
even with new WHERE or LIMIT clauses.

Is mysql creating a 'temporary' index and caching it? Is the original
10 second query the actual time for my query to execute? I'm finding
it difficuly to optimise my query as I'm not sure exactly which times
are correct.

---

Mysql Cache While HotCopy Runs
What I am looking for is if MySQL has the ability to cache Transactions while the database is locked (i.e. doing a Mysqlhotcopy or possible starting replication) whether in a buffer or possibly temporary table and then to commit this data to the database once the tables are unlocked.

Obviously it would be better if the buffers were able to be defined at differing sizes.

---

Can I Cache My Whole Db?
I would like to know if I can cache my whole database. I have a couple of databases. All of them are very small, less then 5MB. Is there a way to cache the whole database in memory? What other ways are there to improve performance?

---

Cache
My web site is pulling from several tables that don't change all that often
and seems to be an ideal candidate for query caching. I tried to enable it
by copying the my-medium.cnf to /etc/my.cnf and restarted the service. From
my query editor I typed SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE' and it
returns ON which is exactly what I want.

I then executed several queries from my site but none of them are being
cached. They're just plain SELECTs in most cases, any ideas why the DB won't
cache them? Do they have to be executed several times first?

When I try and check Qcache_queries_in_cache I get an empty set, likewise
for Qcache_not_cached.

---

Cache
My web site is pulling from several tables that don't change all that often
and seems to be an ideal candidate for query caching. I tried to enable it
by copying the my-medium.cnf to /etc/my.cnf and restarted the service. From
my query editor I typed SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE' and it
returns ON which is exactly what I want.

I then executed several queries from my site but none of them are being
cached. They're just plain SELECTs in most cases, any ideas why the DB won't
cache them? Do they have to be executed several times first?

When I try and check Qcache_queries_in_cache I get an empty set, likewise
for Qcache_not_cached... so it's not caching any and it's not not caching
any.

---

Splitting Key Cache
In mysql 4.1, there are new ways to save different index files into
different key_cache segments. The idea is to remove problems with
key_cache locking if another process is accessing it.

As this involves maintenance, etc....

Question: Has anyone seen any measurable improvement in doing this,
vs. just creating a huge key_cache and being done with it.

---

Key Cache Buffer
It grows and grows and causes the mySQL client to throw an Out of Memory error. The only recourse I have is to reboot.
There must be a way to flush this without a reboot and I can't find it in the manuals.

---

Connection Seems To Cache Data
I open MySQL Manager and connect to my database.

I then insert one record in to an InnoDB table in MySQL (4.0 and 4.1
alpha hosted on XP running mysqld --console).

I then retrieve the record successfully in MySQL Manager using the
existing connection. This indicates that commit is running
successfully, (I have seen this on the database trace too).

I then try to retrieve the record (through a small MySQL/TomCat
application). Sometimes it is retrieved successfully, and sometimes an
empty record set is returned.

If I reduce connection pooling (on the connection script (in
components.xconf))to min 1 max 1 connections, the record is retrieved
successfully every time.

This seems to me to indicate that existing connections created for my
TomCat app are searching on an old (maybe cached?) version of the
database. However, when by chance I get the same connection I used to
insert the record, the record is retrieved successfully.

However, the record is retrieved more often than not even when there
are 10 connections pooled, although I understand that connections are
not round-robbined and some are used more commonly than others,
supporting my suspision.

This is the only insert that uses transactions in my app, and it works
fine with Oracle. All other inserts (not using transactions) work fine
on both database servers (and both MySQL 4.1.1-alpha and 4.0.16).

---

Connection Seems To Cache Data
I then insert one record in to an InnoDB table in MySQL (4.0 and 4.1
alpha hosted on XP running mysqld --console).

I then retrieve the record successfully in MySQL Manager using the
existing connection. This indicates that commit is running
successfully, (I have seen this on the database trace too).

I then try to retrieve the record (through a small MySQL/TomCat
application). Sometimes it is retrieved successfully, and sometimes an
empty record set is returned.

If I reduce connection pooling (on the connection script (in
components.xconf))to min 1 max 1 connections, the record is retrieved
successfully every time.

This seems to me to indicate that existing connections created for my
TomCat app are searching on an old (maybe cached?) version of the
database. However, when by chance I get the same connection I used to
insert the record, the record is retrieved successfully.

However, the record is retrieved more often than not even when there
are 10 connections pooled, although I understand that connections are
not round-robbined and some are used more commonly than others,
supporting my suspision.

This is the only insert that uses transactions in my app, and it works
fine with Oracle. All other inserts (not using transactions) work fine
on both database servers (and both MySQL 4.1.1-alpha and 4.0.16).

---

How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post):
*table person with fields:
-persid: autoincrement id
-name: name of the person
*table material with fields:
-materialid: autoincrement id
-material: name of the material eg "wood"
*table color with fields:
-colorid: autoincrement id
-color: name of the color eg "green"
*table persmaterial with fields:
-persmatid: autoincrement id
-persid: link to table person
-materialid: link to table material
*table perscolor with fields:
-perscolorid: autoincrement id
-persid: link to table person
-colorid: link to table color

In the webapplication the search can be entered by the users as a kind of pseudo query:
(color=red OR color=blue) AND color=green AND material=iron

My question is: how can I automatically transform this pseudo query into an efficient MySQL query?
I have tried out some different options:


Option 1:
(SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION
(SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1))
Remarks:
*I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query?


Option 2:
SELECT persid FROM person p WHERE
(EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid)
OR
EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid))
AND
EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid)
AND
EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid)
Remarks:
*very easy to get from pseudo query to MySQL query but what about performance?

Option 3:
SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE
p.persid=pc.persid
AND
(pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3)
AND p.persid=pm.persid
AND pm.materialid=2
GROUP BY p.persid HAVING
sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1
AND
sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1
AND
sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1
Remarks:
*this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form;




Option 4
SELECT DISTINCT pc1.persid FROM perscolor pc1
INNER JOIN perscolor pc2
ON pc1.persid=pc2.persid AND pc2.colorid=2
INNER JOIN persmaterial pm1
ON pc1.persid=pm1.persid AND pm1.materialid=2
LEFT OUTER JOIN perscolor pc3
ON pc1.persid=pc3.persid AND pc3.colorid=1
LEFT OUTER JOIN perscolor pc4
ON pc1.persid=pc4.persid AND pc4.colorid=3
WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL
Remarks:
*this option requires the pseudo query to be turned into a product of sums form

Option 5:
SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid
Remarks:
*very easy to get from pseudo query to MySQL query but what about performance?



-- phpMyAdmin SQL Dump
-- version 2.6.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 19, 2006 at 01:13 PM
-- Server version: 4.1.9
-- PHP Version: 4.3.10
--
-- Database: `aston`
--

-- --------------------------------------------------------

--
-- Table structure for table `color`
--

CREATE TABLE `color` (
`colorid` int(11) NOT NULL auto_increment,
`color` varchar(30) NOT NULL default '',
PRIMARY KEY (`colorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `color`
--

INSERT INTO `color` VALUES (1, 'red');
INSERT INTO `color` VALUES (2, 'green');
INSERT INTO `color` VALUES (3, 'blue');
INSERT INTO `color` VALUES (4, 'yellow');

-- --------------------------------------------------------

--
-- Table structure for table `material`
--

CREATE TABLE `material` (
`materialid` int(11) NOT NULL auto_increment,
`material` varchar(30) NOT NULL default '',
PRIMARY KEY (`materialid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `material`
--

INSERT INTO `material` VALUES (1, 'wood');
INSERT INTO `material` VALUES (2, 'iron');

-- --------------------------------------------------------

--
-- Table structure for table `perscolor`
--

CREATE TABLE `perscolor` (
`perscolorid` int(11) NOT NULL auto_increment,
`persid` int(11) NOT NULL default Ɔ',
`colorid` int(11) NOT NULL default Ɔ',
PRIMARY KEY (`perscolorid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `perscolor`
--

INSERT INTO `perscolor` VALUES (1, 1, 1);
INSERT INTO `perscolor` VALUES (2, 1, 2);
INSERT INTO `perscolor` VALUES (3, 2, 1);
INSERT INTO `perscolor` VALUES (5, 3, 3);
INSERT INTO `perscolor` VALUES (6, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `persmaterial`
--

CREATE TABLE `persmaterial` (
`persmatid` int(11) NOT NULL auto_increment,
`persid` int(11) NOT NULL default Ɔ',
`materialid` int(11) NOT NULL default Ɔ',
PRIMARY KEY (`persmatid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `persmaterial`
--

INSERT INTO `persmaterial` VALUES (1, 1, 1);
INSERT INTO `persmaterial` VALUES (2, 1, 2);
INSERT INTO `persmaterial` VALUES (3, 2, 1);
INSERT INTO `persmaterial` VALUES (5, 3, 2);

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

CREATE TABLE `person` (
`persid` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL default '',
PRIMARY KEY (`persid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `person`
--

INSERT INTO `person` VALUES (1, 'john');
INSERT INTO `person` VALUES (2, 'emily');
INSERT INTO `person` VALUES (3, 'liz');

---

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.

---

MySQL Error 2013: Lost Connection To MySQL Server During Query
I'm running sugarcrm on mysql and the following query is returning "MySQL error 2013: Lost connection to MySQL server during query":

---

Different Results In Mysql & Mysql Query Browser?
I've come across an extremely strange problem. The exact same query in
both mysql command line client, and mysql query browser gives entirely
different results. I was hoping someone out there could shed some
light.

Ok, the query (I've stripped it bare, the real query is a bit more
complex)...

Each person in the people table has an associated 'place', which is an
integer that maps onto a suburb in the suburbs table. The 'place' CAN
be NULL.

so, for all intensive purposes, the schema is roughly:

---

Help With Php/MySQL Query?
I'm trying to pull records from a MySQL database using a sql query - to pull records where the "exp_date" field is greater than or equal to today. How do I do this?
Here is my query:
PHP
$query = "SELECT v_title,link_id,v_descr,hw_added,hw_region_id,approved,user_approved,exp_date FROM ec3_ad WHERE hw_feat=1 AND approved=1 AND user_approved=1 AND hw_region_id=103 AND exp_date>=strftime('%d %b %Y %H:%M:%S') ORDER BY hw_added DESC LIMIT 20";
... but this is obviously not working because I don't know how to add today's date/time dynamically to the query.

---

Can Anyone Help With This Mysql Query?
I'm using the following query to join the 3 tables gallery_category, gallery_photos and spectacle:

PHP

SELECT gallery_category.id AS gcid, gallery_category.name AS gcname, private,
COUNT(photo_id) AS countim, spectacle.id AS sid, spectacle.name AS showname
FROM gallery_category
LEFT JOIN gallery_photos ON gallery_category.id=gallery_photos.photo_category
LEFT JOIN spectacle ON gallery_category.spectacle=spectacle.id
GROUP BY gallery_photos.photo_category
ORDER BY gallery_category.name ASC

It seems to work fine, but it will only return 3 rows. If I add a new entry to the gallery_category table, the result from the query doesn't return it. If I then add an entry to the gallery_photos table with the new gallery_category in the column gallery_photos.photo_category (i.e. I add a photo to the new category), then the new category will be returned in my result set. My first thought was that it was ignoring categories with no photos in, but this is not the case (one of the 3 rows returned has no photos - countim=0).

---

MySQl Query
SELECT *
FROM test_downloads, test_download_ride, atm_rides, atm_areas, atm_area_ride
WHERE atm_areas.area_id =1
AND atm_area_ride.area_id = atm_areas.area_id
AND atm_rides.ride_id = atm_area_ride.ride_id
AND test_download_ride.ride_id = atm_rides.ride_id
AND test_downloads.download_id = test_download_ride.ride_id

I would like to keep the "and" format for the time being instead of using joinsor any other method.

---

Mysql Query Help
I have 2 tables in my database that I want to link
in policy table I have an orgid and a policyid
in the policypermissions table I have the permissionid and policy id.

my problem is that I have a bunch of duplicate policiepermissions assigned to different policyid's and I want to delete a specific permission id

Quote:

select count(p.orgid), p.orgid, pp.policyid from policypermissions pp join policies p on
p.policyid=pp.policyid where permissionid=7 group by p.orgid Having (count(p.orgid) >1)

the count for the orgid is right but I need to see all of the policyids for each orgid. is there a query that I can run that will delete all but the first policyid?

---

MySQL Query, Is It Possible?
I have the following table-structure:

countID
countItemCode
countIPaddress
countWhat (in or outclick)
countReferer

Is it possible to make a mySQL query that outputs the following

#38AFG66
total clicks: 34
(in:12,out:20) referrer (site1:10,site2:12,site3:2)

#JHGS676
total clicks: 45
(in:31,out:14) referrer (site1:8,site2:7,site3:5,site4:25)

---

MySQL Query Help
I am sure you should be able to do this using Join.. but its a tad bit advanced for me

I need to get the value of admin and super_admin from the table groups selecting by the column called name. But to get the value to select by the column name you need to get the value of the column called group in the table users selecting by the column called username.

---

Fed Up With MySql, Can Someone Help Me Run This ****ing Query?
I come from MS SQL 2005 background and I cannot get this MySql query to run..

INSERT INTO [lookup] ([Type],[Key],[Value],Rank,Status,Created) VALUES (1,2,'a',1,1,CURRENT_DATE());

Why won't this run??? (I know type, key, value are MySql "keywords" BUT I use the column definitions []). In MS SQL 2005, this works fine perfectly.


Also, how the ***** do I run multiple sql statements in MySql query browser? Which ****** developer wrote it so you can only run one statement at a time? It's these little annoying ****** that make me switch back to MS SQL.


Also, how the ***** do you declare variables in MySQL WITHOUT using a ***** stored procedure?

Here's what I want to run IN MS SQL 2005:

DECLARE @Id INT; SET @ID = 0; DECLARE @ResultDate Date; SET @ResultDate = GETDATE();
Begin
IF @ID > 0
BEGIN
INSERT INTO User (Name, Created) VALUES ('test', @ResultDate); SELECT @ID = @@IDENTITY;
END
ELSE
BEGIN
UPDATE User SET Name = @Name, Modified = @ResultDate WHERE ID = @ID;
END

SELECT @ID as ID, @ResultDate as ResultDate

Try and Convert that into MS SQL (INLINE SQL...not a function, not a procedure, INLINE SQL).

Is this possible in MySql? If it isn't, I'm done with open source crapola. People keep talking how Microsoft sucks, but at the end of the day...Microsoft actually has products that get the job done.

---

MySQL Query
Can someone help me or point me in a way for help coding the mySQL query thing so I can run querys?

Do I run the querys on PHP stuff?

---

MySQL Query Help Please...
How do i select full names from a table which start from any number ?
I am having a table named users in which i am simply retriving records by alphabets for eg. All names starting with A or B or C...i am getting this done by using the following:

sql = "SELECT * FROM users WHERE fullname = 'A%'";

But i don't know how to retrieve all records which start from 1,2,3,4,5,6,7,8,9,0 all at once. I will all records to be retrieved at once which start from a number or who's first char. is a number.

---

Mysql Query Log
I have used mysql 5.0 alpha and php to build a shop cart system.I installed all of those in windows. Coz I would like to set up a common query log, so i use "mysqld --log=fileName" to generate a log file。The result is the log file has been successfully generated, but there is nothing inside .

For exampe, I run "mysqld --log=test.log" （test.log is the log name given by myself). In the data folder, there is an file called "test.log" has been generated. There is not any log information inside except those general information. Even I have done some searching SQL command not only in the shop cart system, but also in the MySQL command prompt, it seems like mysql never write anything into that log file. I don't know what is going on here or is there something I should do but I didn't do or something else. Please help me.

The following are the contents in my log file. That's all of them, no matter how long I wait.
-----------------------------------------------------------test.log
mysqld, Version: 5.0.0-alpha-max-debug-log, started with:
Tcp port: 3306 Unix socket: MySQL
Time Id Command Argument

---

Query Across DBs In MySQL
I'm new to MySQL and am wanting to be connected to the MySQL server and
query across diffrent db's. To my understanding a different db in MySQL
is the same as a schema in Oracle. Am I correct in this thinking?

What I want is to have one DB called projects which has common tables
such as contact_details, postal_codes, language and so on. Then a
number of other db's for different projects. Then project1 has its own
tables but can also see those in the projects db.

---

MySQL Query
I have a query:

$query = "SELECT id, catid, title, description, date, updated, hits FROM #__weblinks "
. "WHERE published='1' AND checked_out='0' "
. "ORDER BY date DESC LIMIT 5";

In this record, there is a record creation date ("date") and a record update date ("updated"). At record creation, the current date and time is entered in the format "YYYY-MM-DD HH:MM:SS" in the "date" field, and the "updated" field is filled with "0000-00-00 00:00:00". When the record is updated, the "date" field remains the same, but the "updated" field is updated with the current date and time.

In my query, it will fetch the 5 most recently added records. I want the query to check if "updated" has a value other than "0000-00-00 00:00:00" and if so use that field to ORDER BY, otherwise use the "date" field to ORDER BY, again, limiting the results to the 5 most recently added OR updated records.

I originally tried using a UNION, but this would return duplicate records if a record was both new and updated within the timeframe that separated the top five results. Obviously I need to use an IF or CASE statement in the ORDER BY, but I could get anything but errors when trying. Any ideas?

---

MySQL Query Help
Anybody know how the following query could be re-written without using the union feature?

SELECT vechicle_plate, vechicle_state
FROM purchase_info
WHERE customer_id = '1'
UNION
SELECT vechicle_plate, vechicle_state
FROM purchase_info, associates
WHERE purchase_info.id = associates.purchase_id
AND associates.customer_id = '1'

---