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?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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?
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?
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?
Should I Use MySQL Query Cache?
The majority of my website's bottlenecks occur due to the database overhead. This is because I run a user community website, where users can send messages, post comments, read/write forum topics, and view profiles. I have been studying to solve how to speed up the http and the website, however I was also thinking about using caching techniques for the database as well (as opposed to only PHP and HTTP). Therefore, since MySQL has it's own Query Cache system, would this be effective. I understand that it would work in a system that has a lot of SELECT queries with little UPDATE and INSERT procedures, but for a user community website which consists of lots of changing data, would it be effective? When and where should Query Caching be used? My Server has alot of RAM and space, so what else should I look out for?
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 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?
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....
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.
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 =]
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).
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.
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).
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.
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.
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');
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles. based on this I now want to find all the individuals that are affiliated to those listed in the first query ....
Big Query - Query Not Completely Stored In Memory
I have this query and when executed in mySQL query window throws error that "Big Query - Query not completely stored in memory". Also executed thru app, the program justs hangs. I have checked all indexes, they all look good.....
Pagination W/1 Query + How To Use Query With Indexes
i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind. i have mysql 4.1.10 1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes. 2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries?
Reusing A Query Output In The Same Query
I am guessing a basic question but not one I can find an obvious answer to. If I create a calculated or modified column in a query (such as a modified text string), and then want to reuse that in the same query as I need to do three or four operations on it in sequence, how do I do it in mySQL 4.1? Do I need to create a new column to store the interim result in an existing table (and then clear or alter it each time I run the query), or create a temporary table, or is there an easy way to reuse the query output in the same query (does the query have a name like a table name)? If it requires a new column or table, are there particular disciplines to ensure it is robust and self maintaining?
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database: PHP $get_stats_newthreads = $db->query_read(" SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible FROM " . TABLE_PREFIX . "thread AS thread WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10) ORDER BY rand() DESC LIMIT 5"); and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid
Using A Query Result In Another Query
Can I use the results of a SELECT query as a "table" in another query? I want to let my user pick a subset of the data, then refine it further. So ... do I have to repeat all the selection criteria at each step, or can I just do refer to the last query result? If so, what's the PHP syntax for this?
Run A Query On The Results Of A Query?
Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array). With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...
Rewriting A Query Without A Sub Query
I've recently changed hosts and found that some of my code broke. The new host is using mysql version 4.0.25 which does not support sub-queries (and they won't upgrade). I'm trying to figure out how to rewrite the following query so it will work on 4.0.25 but not getting anywhere.....
Is It Possible To Run A Query On The Results Of A Query?
I have been trying to figure this out but no luck. Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order. I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So: SELECT * FROM order_log WHERE product's group_id = 1.
|
TRACKER
