Large Query Times
SELECT distinct column1 FROM table1, table2 WHERE column1 <> column2 ....
View Complete Forum Thread with Replies
Related Forum Messages:
Measuring Query Times
I have read a few posts here and see that some people have questions regarding the length of time it can take to perfrom their queries. I would be quite happy reaching that stage because at least I would know how long mine take. :| How can the time delay be forced to output? Using MySQL 5.0.
View Replies !
Report Of Query Execution Times
Does mysql have a tool to record and report a list of all queries executed and how long it took to execute each query? If not can anyone suggest a tool I can use to do this? I've heard of mysql query profiler but does it capture queries from all sessions?
View Replies !
How To Find Out How Many Times A Query Has Been Executed
How can you count the number of times your query has been executed? So I'm trying to set it up, so that I get a variable on every time someone views a review. I have two querys right now, but honestly don't really know what I'm doing. And I'm assuming I have to increment viewcount everytime that query gets executed. This query looks up the review:
View Replies !
Query Single Table, Multiple Times
Before i narrate my issue, i would like you to preview my table structure : Table name = machine_info +------------+----------+------------+ | machine | tag_name | tag_type | +------------+----------+------------+ | machine101 | sge | farm | | machine101 | US | site | | machine101 | CRITICAL | status | | machine102 | CANADA | site | | machine102 | UP | status | | machine102 | sge | farm | | machine103 | CHINA | site | | machine103 | DOWN | status | | machine103 | sge | farm | | machine104 | US | site | | machine104 | CRITICAL | status | +------------+----------+------------+ Requirement : I would like have machines from all 'sites' (almost 15 in the actual db) with 1)status='DOWN' 2)status='CRITICAL' 3)status='UP' Example: (Here the results would be like) SITE|DOWN|CRITICAL|UP US |0 | 2 | 0 CANADA|0|0|1 CHINA|1|0|0 I use php to extract information, here's what i do : <?php $db = mysql_connect("hostname", "user", "password") or die(mysql_error()); $selected = mysql_select_db("dbname") or die(mysql_error()); $site_codes = mysql_query("select distinct(tag_name) from machine_info where tag_type='site';"); echo "SITE|DOWN|CRITICAL|UP ";
View Replies !
Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing
View Replies !
Mysql_fetch_array() And Large Query
I am running a SELECT query on a table of about 1.6 million records. The number of rows I get back is about 1.5million. SQL runs fine, in command line and php, but when I try to move data from 'resource' to an array with mysql_fetch_array() my browser stalls. So my question is, am I overflowing the buffer somewhere, and if anybody has a clue how to fix this.
View Replies !
Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username FROM (( " . phpbb_forums . " f LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id ) LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id) ORDER BY f.cat_id, f.forum_order"; Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions? The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?
View Replies !
Noob: Select Large Query
If I had to select large number of records, say 1 million from the mysql server. What problems would I be facing? MySQL connection timeout, network latency? Anyone has done a large simple mysql select query and what problem did you face? I am not doing any joins.
View Replies !
Retreiving Large Query Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy. This means that records from the subsequent queries might have records that already appeared in the first query or that some records will be left out. For performance issues it is a problem to add a unique secondary sorting criteria (like id) to the query. Is there a clean way to force MySQL to relate to the first (initial) query result set?
View Replies !
Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do: There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns. The most common query is SELECT data FROM table WHERE n1 = ? The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column. Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset: SELECT COUNT(1) AS total FROM table WHERE n1 = 3; index = random number from 1 to total SELECT data FROM table WHERE n1 = 3 LIMIT index,1; How can I speed this up? I need it to take less than half a second if possible. Thank you.
View Replies !
Batch Query Or Large List Of IN() Arguments?
I'm receiving a potentially large list of productID numbers from an external data source. Each productID has a number of partID's associated with it and those partID's are stored in my database. I would like to create a list of products sorted by the number of parts each has. If I only have one table called "part" which stores a partID and its associated productID, which of the following methods would be more efficient assuming the application and DB are on the same server. Option A) SELECT productID, Count(*) counter FROM part WHERE productID IN(large list of productID's received, maybe 1 or >10000) GROUP BY productID ORDER BY counter DESC LIMIT ?, 10 Option B) Execute a batch query of prepared statements (SELECT COUNT(*) FROM part WHERE productID=?) for each product in the received product list then sort the returned results in the Java app that is making the query. Option C) ???
View Replies !
Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions: 1-what can I do to avoid the system stop or time out message? 2- To use several tabels infomation should I use 'View' command or can I use other methods? 3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table? (I use postgresql).
View Replies !
Query On Large Table When Implementing Paging With LIMIT?
I'm working on a paging implementation but I am stuck on a couple of things: 1) I tested with a simple query which yielded this: EXPLAIN SELECT create_date FROM threads LIMIT 0,25 id=1 select_type= SIMPLE table=threads type=index possible_keys=NULL key=create_date key_len=4 ref=NULL rows=57852 extra=Using index I thought that the LIMIT clause would limit the number of rows scanned, why is it still showing 57k rows? 2) Assuming there is a simple fix to the above, now if I want to order by create_date (SELECT create_date FROM threads ORDER BY create_date LIMIT 0,25) I understand that mysql must pull the entire table to sort the data set before applying the limit. So the explain would probably look a lot like the above. In this instance, is there a more scalable solution to implement a sorted paged result set?
View Replies !
Appear 2 Times
i'm having this problem finding out the right sql query. i have two tables A & B the userid in table A appears as FK in table B i need to list userid that appear 2 times or less in table B which has a where clause column3='Yes' how do i go about it>?
View Replies !
Comparing Times
I'm creating a voting system, and want to prevent people from cheating. Therefore I'm logging their IP, and the time they voted, and what I'm planning to do is have a script which will delete any records which have been in the database for say 24 hours. To get the length of time I was gonne use: HOUR(TIMEDIFF(NOW(),time))
View Replies !
Number Of Times
I have a query that grabs 500 rows from 1 field within 1 table. In this table. Each user will have 500 rows associated to them from within that table. So if there are 2 users then there will be 1000 rows. Each time 1 user executes the query, it grabs their 500 rows. If 2 users execute the query simultaneously, then this means that there are 1000 rows, all of which will be selected; 500 by one user and 500 by the other. If 3 users access the table simultaneously, then there are 1500 rows and so on. My question is how many users can execute this query simultaneously before we see performance loss?
View Replies !
Records Between Two Times
I have a table for a restaurant database with two fields open_time and close_time in TIME format. I want to be able to select all records that fall between open_time (when the restaurant opens) and close_time (when the restaurant closes). I have this query: Quote: Select hours_id from hours where curtime() between 'open_time' AND 'close_time'; It partially works, but its not working if the close_time is after midnight (i.e 1am = 01:00:00 in database). I can't figure out the issue with the difference between AM and PM.
View Replies !
One To Many Multiple Times
I have a 'links directory' database that currently contains 4 tables: category, subcategory, region and links. My links table contains company names and, among others, three fields catID, subcatID and regID. Each of these are related to primary keys in the other three tables, category (catID), subcategory (subcatID) and region (regID). My issue is that the records in the links table can be in multiple regions, multiple subcategories and multiple categories, but the way I have it designed, each record in links can only be associated with a single catID, subcatID and regID. Currently the only workaround I can think of it to have duplicate entries in the links table for a single company, but with every variation of catID, subcatID and regID. This will result in a very large table, but I cannot currently think of any better idea.
View Replies !
Dates And Times.
You can't have two timestamp columns in a database both with the default now(). - why I would want to is not the issue. You can't use the data and time functions, such as CURRDATE()/CURRTIME() provided by MySQL as defaults for date/time type columns. Is there a way to use something like 'select currtime();' as the default for a column? so my question is, how do I use ANYTHING other than one timestamp with the default value of now() for date/time defaults in MySQL.
View Replies !
Run-times On SQL Commands
Does anyone know where I could find explanations/descriptions of runtimes on sql commands like select and functions like max(). In other words if these are linear functions/commands or log based?
View Replies !
Dates And Times
I have a field in a DB called "customerJobDate" which is configured to store full dates (ODBC dates) e.g. "2006-03-06 08:30:00". Now this does work i can update insert and delete the records perfectly, But if i query the DB asking for all dates that match 2006-03-06 regardless of the time (Date only) then the result returns 0 rows unless any of the dates in the column have 00:00:00 as the time. This works: 2006-03-06 00:00:00 This does not work 2006-03-06 08:30:00 I hope someone can help me as I am finishing this app and this is the only thing holding me back.
View Replies !
Upload Times
I need to upload 2 star catalogues to the mysql database. the first time i tried this i filled up the HD in 2 Days, my instructor mentioned that journalling was taking up most of the space and decided to remove-it. now, with journalling turned off, i have started re-uploading the catalogues, but have noticed a significant decrease in "write" speed. my question is, does journalling speed up writing data into mysql tables? i have roughly 180GB of data to upload, and it took me 4 days to upload 3GB's when last time it took me this time to upload 100GB.
View Replies !
About Dates And Times
I have form with drop down boxes, where folks enter dates and times, I have used implode and explode to format the dates, now I want to write those dates to mysql, I know mysql has its own date and time functions, is there a way to convert my php formatted dates and times into something that mysql will recognize and store as a time and date or does php have a function that will convert it and then allow it to be stored in date/time format in the database?
View Replies !
Ordering Times
i have some vlaues in a database, they are 12 hour times, how would i go about sorting it so 12 comes before 1, as it is its going 1 2 3 4.... 12 etc etc, do i have to use a 24 hour time? then from there function it to show 12 hour when displayed
View Replies !
Export Records Times Out
I try to export all records from my db about once a week. I use phpmyadmin and select all tables structure and data and export as file. Well the problem now is it always times out when I do this. The db only contains about 18 tables and maybe 20,000 lines of data. The tables average about 12 fields each. So now I'm exporting a few tables at a time. Is there something I can do to change this?
View Replies !
Joining Mutiple Times
I have the following situation and I can't figure out what method to use. I have a table with items, for these items I want to know which user inserts a row and I want to know who updates a row. In the table I store both user-ids. The user information is stored in an other table. How can I link both user names to the one row in the items table?
View Replies !
Left Join 2 Times
SELECT f.id as id, f.forum_name as forum, f.forum_desc as description, count(forum_id) as threads, u.name as mod FROM forum_forum AS f LEFT JOIN forum_posts AS p ON f.id = p.forum_id AND p.topic_id=0 LEFT JOIN forum_users AS u ON f.forum_moderator = u.id GROUP BY f.id Output: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod FROM forum_forum AS f LEFT JOIN forum_posts AS Does this query contain any syntax errors? Can mysql perform left join 2 times?
View Replies !
Can't Connect At Peek Times
Can't connect to local MySQL server through socket '/tmp/mysql.sock' error raise when website peek time, current using 3rd party abstract layer, i have checked mysql_pconnect is not in using. anyone know what might cuase this error? mysqlId setup: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 1M read_buffer_size = 2M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 32M max_connections=10000
View Replies !
Call To Database Times Out
using ZenCart 1.3.7, and when it calls to the database after clicking on "go to checkout" it hangs and then returns the following error message: I get this message after clicking on "go to checkout" 2013 Lost connection to MySQL server during query in: [select sum(tax_rate) as tax_rate from (tax_rates tr left join zones_to_geo_zones za on (tr.tax_zone_id = za.geo_zone_id) left join geo_zones tz on (tz.geo_zone_id = tr.tax_zone_id) ) where (za.zone_country_id is null or za.zone_country_id = 0 or za.zone_country_id = '223') and (za.zone_id is null or za.zone_id = 0 or za.zone_id = '12') and tr.tax_class_id = '1' group by tr.tax_priority] 2006 MySQL server has gone away in: [select count(*) as total from sessions where sesskey = 'd60f1f220b761a5a176738c1a65de4b2'] Warning: Unknown(): A session is active. You cannot change the session module's ini settings at this time. in Unknown on line 0 I've checked with my host, and they are able to do a php call to the database just fine.
View Replies !
Listing Queries And Times
how can I list the queries and how long they each take? I've done it before but forgot how; Currently I've got the TOTAL query time on each page but I'd like a more advanced breakdown.
View Replies !
Struggling With Dates And Times!
I'm using MySQL 3.23 - I know it's old but it comes as default on Linux distribs and I just want something consistent between home, my ISP and work. I want to work out the time difference between two entries on my database (I'm using PostNuke and have two posts which are stored as integers, ie unix times). I can see that there is now a datediff() and timediff() function but they don't work hence why I mentioned the version I'm using. Can anyone tell me how to get the difference between two dates - I am assuming I subtract one from the other but don't know how to convert the result into something useful (like days and hours etc) because I'm not sure what the unix time is made up of. I guess an easier version of that lot is : is a unix time just a second count?
View Replies !
Subtract Two Event Times
I am trying to formulate a complex query to get some good info straight from SQL, rather than post-processing in XL. I have a table as follows: EventID Integer (key) ActionID Integer, 1 = event on, 2 = event off dtStamp Date I want to get the duration of each Event. I.e. I need, for each Event: dtStamp(actionID = 2) - dtStamp(actionID = 1) I was going to try: >create view EventOnTime as select EventID, dtStamp as EventOnDtStamp from EventLog where ActionID = 1; >create view EventOffTime as select EventID, dtStamp as EventOffDtStamp from EventLog where ActionID = 2; >select eventOnTime.EventID, eventOnTime.EventOnDtStamp - eventOffTime.EventOffDtStamp as EventDuration where eventOnTime.EventID = eventOffTime.EventID; ... but I can't create views with this version of SQL (server version: 4.0.20-standard-log)
View Replies !
Adding 1 Year To All Times
suppose that dbX -> tableX id columnX 1 1190815874 2 1192975748 3 1196695027 4 1196796789 how can i add 1 year all value where year is 2007 ? my sql do not run UPDATE `tablex` SET `columnx` = DATE_ADD(`columnx`, INTERVAL 1 YEAR) WHERE DATE_FORMAT(FROM_UNIXTIME(`columnx`),'%X')) = 2007;
View Replies !
Dump File Times Out?
I have a huge Access database (about 50,000) records. I use Access dumb to create a sql file which I can them go into phpmyadmin and import the records. Well, after a while the process times out. Is there a way I can import large amount of data into MYSQL without timing out? Also the access database that I convert over gets updated every week. I don't know which records get updated or added. Also the app I am using only works with Access so I can not completely convert over. Is there a way I can update the MYSQL without having to import that huge file again. Maybe something that will not override the records that are same just change the modifed ones and add the new records.
View Replies !
Slow Loading Times
It takes more than 30 seconds to load the phpmyadmin page. On a localhost server. It says Fatal error: Maximum execution time of 30 seconds exceeded in c:appservwwwphpmyadminlibrariesmysql_wrappers.lib.php on line 101 This sometimes also happens when loading my local website that uses MySQL. I've tried reinstalling, but it doesnt help.
View Replies !
Datetime And Timestamp Recording Different Times
I have a datetime field that records date and time. This field records when rec is created. I also have a timestamp field that records whenever the record is updated. I noticed that when i first create the record, the update time is about 3-5 minutes before the create time. The update time and create time has to be identical. How can one lag the other? Im executing sql from a php page.
View Replies !
Trace SQL Queries Date/times
I have a web application connected to mySQL 4.1 database. I would like to know the date/time for the queries executed on mySQL DB when any operations performed from the UI. Is there any mechanism to trace the information related to any query updates happening on the DB when operations are performed from the UI.
View Replies !
Page Times Out But Works After Refresh
I have been developing a page that displays survey results data. There are many SQL queries but has worked so far. Recently when I click the link to the page the page times out but if I refresh it displays the data correctly. Are the results of a query stored so the refresh of the page works quicker?
View Replies !
Subtraction Of Times Inconsistent On Different Machines
query structured as follows: SELECT time(c.start_time), time(c.end_time), time(c.end_time) - time(c.start_time) as 'opening_hours' FROM my_shop c; The data in start and end time is 1970-01-01 09:00:00 and 1970-01-01 17:00:00 respectively. On my local machine this this query returns: | 07:00:00 | 19:00:00 | 12 | However on a remote machine (production) it is returning | 07:00:00 | 19:00:00 | 120000 |
View Replies !
Joining A Table Multiple Times
I was wondering if it was possible to join 2 tables on 3 different feilds, but I don't want the 3 feilds to limit my results... I'll try and explain: Table 1 = "offices" Table 2 = "employees" The offices table has the addresses of all the offices and 3 extra feilds "manager", "supervisor", and "employee". (these 3 feilds have a numeric value equal to the primary key in the employees table) The employees table has the employee information like address and phone number and such. I would like to write a query that would pull the office name as one feild, followed by "manager", "supervisor" and "employee". Currently I have this, but it only pulls the employee and I'm not sure how to get the manager and supervisor to be a part of the query: SELECT CONCAT(office.id_store, ' - ', office.storename) as offices, CONCAT(user.lastname, ', ', user.firstname) as fullname FROM office LEFT JOIN user ON office.empoyee = user.id_user
View Replies !
Ordering Results By Number Of Times They Appear
I'm not really sure the best way to explain what I want to do, so I'm going to make a really simple example to illustrate what I mean. Let's say I have a table with column "temperature". There are 5 entries that contain the word "hot", 3 entries that contain the word "medium", and 2 entries that contain the word "cold". However, these entries are not in any particular order. Is there a "SELECT * FROM" statement which will return the results by order of popularity in the following manner?: hot (5) medium (3) cold (2) I have a script I'm working on and I'm trying to figure out the best way to do something like this.
View Replies !
Select Same Row Multiple Times Using Column
I've seen this asked before and although someone answered that it is possible, they did not post the solution, so if anyone can enlighten me that would be helpful. Basiclly with table info name | quantity john | 3 anne | 1 will | 2 Return results john john john anne will will
View Replies !
Check Same Column Multiple Times
What would like help with is how to best structure a query that will select user_id and username from Table1 where user_id matches user_id in Table2 and BOTH (group_id = 6 where value = 10) AND (group_id = 8 and value = 15) in Table2 (or even more comparisons) If both conditions are not met in table two, then no results are returned.....
View Replies !
Linking Table Entries Multiple Times
I have no idea how to handle certain kinds of relations between tables: I have a table called 'labels' and a table called 'artists'. Every artist belongs to at least one label, but could also belong to any higher number of them. How would I solve this in my database architecture? I discarded the idea of simply adding 10 columns called 'label1', 'label2', etc; Also I have found the ENUM and SET datatypes, but the possible values entered there should be set at table creation time which is also not quite what I'm looking for.
View Replies !
|
TRACKER
