Any Tips For Improving This Query Time?
Is there anything I can do to improve execution time for this query?
mysql> SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5;
+---------------------------------------------------------+---------------------------------------------------------------+-------+
| webpageUrl | webpageName | count |
+---------------------------------------------------------+---------------------------------------------------------------+-------+
| http://www.w3counter.com/ | W3Counter - Free Web Counter, Web Stats, Live Analytics | 2490 |
| http://www.w3counter.com/stats/visitors/4071/day/1000/0 | Visitors Overview - www.pitbullmortgageschool.com/ | 1496 |
| http://www.w3counter.com/stats/4071/v_daily | W3Counter - Visitors by Date - www.pitbullmortgageschool.com/ | 1130 |
| http://www.w3counter.com/stats/ | Your Websites | 1124 |
| http://www.w3counter.com/stats/websites | W3Counter - Websites | 800 |
+---------------------------------------------------------+---------------------------------------------------------------+-------+
5 rows in set (0.88 sec)
mysql> explain SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5;
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| 1 | SIMPLE | _1_log | ALL | NULL | NULL | NULL | NULL | 29422 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
1 row in set (0.00 sec)
It averages 0.5-3 seconds throughout the day (it's run often enough that it shows up in my slow query log multiple times an hour for exceeding 1 second).
Adding a partial index on webpageUrl doesn't affect anything, and URLs can be quite long. Is there anything I can do to speed up that query? The table is updated too often to maintain separate url/count tables -- can't spare the resources.
View Complete Forum Thread with Replies
Related Forum Messages:
Tips For Improving The Performance Of Mysql
I really hope I can get some general advice and suggestions about how I can improve the performance of our mysql server. We have a dedicated webhost running a number of quite highly used websites. They all use mysql quite heavily at times. Recently we've had complete mysql gridlock and had to restart the server. What are the steps I can take to improve performance? This is what i've done so far. 1. Optimise queries - i've been checking the queries we are running and trying to cache the results in a session rather than generating all the time. I've also installed something called mytop which is basically the same as the standard unix top command, except for mysql processlist and shows you the queries currently running and the time elapsed. 2. I have run explain on my queries and put in indexes all over the place to try and make lookups quicker. Things i'm considering doing 1. Reducing the amount of data in the database and perhaps rotating the tables slightly, our biggest tables are approaching 2 million records and linking these together is i'm sure causing some of the problem, especially when we have to run a SUM query on them, even with good indexes. 2. Moving the database off the webserver and onto its own dedicated mysql server. 3. Load balancing the database and somehow clustering more than one database server, but this would cause serious headaches so not seriously considering unless it was thought the best solution. 4. Upgrading mysql... We are currently running version 4.0.27, should we upgrade to 5? Will this give us performance boost? 5. Re-compiling... After a google search I discovered that potentially we could recompile mysql with a better options set and perhaps improve performance. 6. Buying a book on mysql optimisation Any suggestions?
View Replies !
Improving LIMIT X,Y Query
I have only 1m records in my database running on a laptop of speed 1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk. I use 'LIMIT x,10' for the query to utilise record paging. When the value of x is nearer to 0, the query speed is fast. Presently, 'LIMIT 0,10', 'LIMIT 50000,10' and 'LIMIT 100000,10' takes about 0sec, 11secs and 4mins respectively. 1) Is there anything I should do, in terms of sql statement or database design, to obtain the same speed for cases where x is in the middle or nearer to the end? 2) How fast is a simple select statement (not select count(*)) on a system like mine? 3) Suppose I left my original select .. limit query as is, what is the minimum hardware that I need to improve the speed?
View Replies !
Help Required Improving Query
the following query has been giving me problems. My users are reporting that credit is going down automatically and download counts seem to be overwritten with other downloads. I'm thinking using JOINS would be better, but I'm not sure which how best to structure it. MySQL UPDATE tb_members s, tb_userfield f,tb_downloads dl SET s.credit_available = credit_available - 1, s.credit_used = s.credit_used + 1, f.field24 = f.field24 - 1, dl.info_downloads_web = dl.info_downloads_web + 1, dl.info_downloads_today = dl.info_downloads_today + 1, dl.info_downloads_30days = dl.info_downloads_30days + 1, dl.info_last_download_time = NOW() WHERE s.userid = '".$ses_userid."' AND f.userid = '".$ses_userid."' AND dl.key='".$download_key."'"
View Replies !
Need Some Help In Improving Performance On A Query (was: Hi People)
I need some help in improving performance on a query which is taking too long to execute. I have 3 tables: table 'photo' stores information on photos and it has 2 important fields: - photo_id - title table 'item' stores information on generic items in the system (photos is an example). It has 3 important fields: - item_id - item_type (like 'photos') - submitter_id table 'user' stores information on users in the system. It has 2 important fields: - user_id - name I would like to get the number of photos that either have a title that contains 'a' or the submitter's name contains 'a' I tried the following: SELECT count(DISTINCT photo.photo_id) NumItems FROM (photo, item, user) WHERE (item.item_id = photo.photo_id AND item.item_type = 'photos' AND (photo.title LIKE '%a%' OR (user.name LIKE '%a%' AND user.user_id = item.submitter_id))); This query is taking way too long (sometimes up to 10 seconds Note that I have over 2,000,000 records in the 'user' table (I suspect the problem is there) I only have 2 recods in the 'photo' table I only have 2 records in the 'item' table
View Replies !
Improving The Performance
Serve Spec: AMD 2000 RAM 1.5 GB Fedora 10 How can I modify my.cnf for a better performance. Current file looks like [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines and adjust the connectstring as needed. #ndbcluster #ndb-connectstring="nodeid=4;host=localhost:1186" [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [ndbd] # If you are running a MySQL Cluster storage daemon (ndbd) on this machine, # adjust its connection to the management daemon here. # Note: ndbd init script requires this to include nodeid! connect-string="nodeid=2;host=localhost:1186" [ndb_mgm] # connection string for MySQL Cluster management tool connect-string="host=localhost:1186"
View Replies !
5.X Optimization Tips
I have a unix box that has 32G of RAM. I want to optimize MySQL because right now it's pretty slow using the default settings. Do you have any configuration settings that you recommend to maximize MySQL [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 512M max_allowed_packet = 16M table_cache = 512 sort_buffer_size = 512M read_buffer_size = 8M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 512M thread_cache_size = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 2G innodb_additional_mem_pool_size = 20M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M Edited 1 time(s). Last edit at 01/26/2009 03:46PM by John Doe.
View Replies !
Performance Tips
I'm working with 5.0.29 and have a model with 35 tables. When I click a table for modification or move the element into de model my computer lost performance and WB take a while to respond (enought to desesperate in daily use). Have a tips page to check posible reasons or tips for a better performance?. My pc have a Intel dual CPU E210 2GHz and 2MB RAM, then I thinks that is enougth for run a normal program. Please send me tips. System info from MySQL WM MySQL Workbench OSS for Windows version 5.0.29 Cairo Version: 1.5.12 Rendering Mode: GDI Rendering OpenGL Driver Version: Not Detected OS: Windows XP CPU: 2x Intel(R) Pentium(R) Dual CPU E2180 @ 2.00GHz, 2.0 GB RAM Video adapter info: Adapter type: NVIDIA GeForce 7300 GT Chip Type: GeForce 7300 GT BIOS String: Version 5.73.22.62.72 Video Memory: 262144 KB
View Replies !
MySQL Tips And Tricks
If you have an interesting MySQL Tip or Trick, I would be interested in posting it. The following link (GPU Free Documentation License) contains advice on such standard features as "showing a process list" and "killing a particular" process, how to do transactions, interface with Perl, Java and C, remove duplicates entries in a table, rollback transactions, merge multiple tables into one, update foreign keys in a multi-user environment, create a UUID (same as GUID for MSSQL users), monitor connections with "tcpdump", create a C or C++ API, encrypt and decrypt data (des_decrypt, and des_encrypt), generate a virtual row count, monitor and show all "select, insert, create etc. statements" with binlogs, and using Spatial Extensions like SELECT X(GeomFromText('Point(5.02 7.9)')); with explanations. Code:
View Replies !
Tips / Advice For Searching A Database
I'm building a search facility that'll be the main focus of my website, with the plan being that it'll be the first port of call for people to browse the products, similar to Amazon. Apart from doing a couple of LIKE commands, is there any advice people can offer and maybe some example queries that will return the most useful results to the user? I'll be searching the following fields: product_name image_name product_description
View Replies !
PHPmyadmin: Export Database: Tips?
Using PHPmyAdmin, is there any tips one can give me when exporting? I plan on using this data in a Textpattern or Drupal or Similar web app/cms/blog... what would be the best way to export the data for ease of importing into (for example) Textpattern... Drupal maybe?
View Replies !
Get Query Time Query Time Using PHP
I just want to ask, how to get query time like this one: SELECT f_name, l_name from employee_data where f_name = 'John'; +--------+------------+ | f_name | l_name | +--------+------------+ | John | Hagan | | John | MacFarland | +--------+------------+ 2 rows in set (0.00 sec) <- This Time <-- Maybe some instruction using PHP?
View Replies !
Time Query - Please Help?
I’m completely baffled by a query that I thought I had working yesterday, but I can’t get it working today. Here’s the screnario: I’m publishing a story to the web and I don’t won’t it to be published live until: -The current date (type: date) is less or equal to now -The current time (type: time) is less or equal to now So here’s my query: select * from cms_stories WHERE section = 'news' AND published_web_date <= NOW() OR published_web_date IS NULL AND (published_web_time <= NOW() OR published_web_time IS NULL) ORDER BY story_id DESC LIMIT 1 Now the query works for the published_web_date, but not for the published_web_time. It seems to ignore that as you can see from the screenshot returned below from that query.
View Replies !
Limiting Query Time
Is it possible to limit the query time in MySQL (3.x or 4.0)? For example, I'd like to have any query that takes more than a specified number of seconds just quit automatically. Seems dumb, but on a web site, nobody is going to wait minutes for a query to return so they refresh anyway. So on a busy server, MySQL ends up with several queries running that all take a long time to finish which compound to make it even slower. A simple time limit would solve the problem. Yes, I know that the queries should take less time, but again, on a busy server, sometimes the longer queries do take a long time (10 minutes or more) to complete.
View Replies !
Add Time Using A Select Query
Hello, how can I add, say, 2 hrs from a time in a database using a select query. I have searched the forums but cannot find an answer. Is it possible PHP Code: select date, `time` as timetime , time_format(`time','%l:%i %p') as Printtime, client, address from..........
View Replies !
Exceeded Run Time Query
I made a application in PHP, I have a table "control1" and this table have 85000 registries and Size is 200 MB, so when I try do a query in PHP, I have a error "exceeded run time", I changed the time but is the same, and after I connected by console, and I could see that the query take 14 minutes. Here my sql: $sql_filtro="SELECT COUNT(*) nFilas FROM control1 WHERE MATCH (contenido) AGAINST ('$q')";
View Replies !
How To Get Time Execution Of A Query?
Do you know how to get the time that a query executes? Is there a formula on how to get it? Example: Note: I have 5000 data in the table cars $result = mysql_query("Select * from cars"); Question: How to get time of executing the query above?
View Replies !
How To Get Query Execution Time
How to get query execution time in "mysqlquery.log" files I am using: Linux 7.3 mysql 4.1 How to display the query execution time for each and every query in log files. i have configured "slow-log-query" and "mysqlquery.log" in "my.cnf" is pasted below. log = /var/lib/mysql/mysqlquery.log log-slow-queries = /var/lib/mysql/slowquery.log long-query-time = 0 slow-launch-time = 1 then i can get a output in "mysqlquery.log" like, 070830 9:24:29 2 Connect root@localhost on 2 Init DB jbdatabase 2 Query select count(*) from vacancy1_table 2 Query select category,listcategory from category_t able order by category 2 Quit but i need to display the "timetaken of each end every executed query" along with the query in the above logfile like, E.g:select category,listcategory from category_table order by category(0.03 secs).
View Replies !
Execute Time Of A Query
I'm running a SELECT * query on a table with over 3,000 rows. I need to know the execute time on the query. PHPMyAdmin shows it to me, but because they automatically add a LIMIT 0, 30 I suspect the time is only for those 30 records. It also has a feature to prevent web server timeouts that prevents me from setting the LIMIT to 0, 3100. BTW, I'm programming in PHP. Does the time to run the query get automatically passed when PHP sends the query to MySQL? <? PHP $Query = "select * from my_table where 1"; $Result = MYSQL_QUERY($Query); ?>
View Replies !
Last Query Execution Time
Is there a way to retreive the elapsed time for the prevously executed query? Alternatively, Is there a way to query the current time in fractions of a second? I am attempting to use a stored procedure to execute and track the time it takes to run some queries and other commands. The logic of the stored proc would go something like this.....
View Replies !
Using Time Functions Within A Query
I'm currently working on a script that searches through a database of restauarant information. One of the things it does is allow the user to search only for restaurants that are open at the time of the search, however i get errors when trying to implement this. Here is the query im using:
View Replies !
Take Long Time To Run A Query!
I have a webserver that I just use for a webapplication. PHP, Apache and MySQL is installed on the server. The size of MySQL database is 10 GB. End inn it is millions of rows. I wonder where I search what is the normail time the data to return? In my case it takes from 20 sec to 2 min to run a query!
View Replies !
Time Query Question
I'm trying to pull dates from a db that are newer than 6 months old. I've been testing with 1 day, since there's nothing in the db that's older than 6 months. query PHP $DATEsql = "SELECT DISTINCT DATE_FORMAT(time_in, '%m/%e/%Y') as time "; $DATEsql .="FROM login "; $DATEaql .="WHERE time_in > CURDATE() - INTERVAL 1 DAY "; $DATEsql .="ORDER BY time DESC "; that is giving me the same results as PHP $DATEsql = "SELECT DISTINCT DATE_FORMAT(time_in, '%m/%e/%Y') as time "; $DATEsql .="FROM login "; $DATEaql .="WHERE time_in < CURDATE() - INTERVAL 1 DAY "; $DATEsql .="ORDER BY time DESC "; This is what I'm interpreting as what I need to do according to the MySql manual.
View Replies !
Query Log With Execution Time?
I have developed a big property portal web site and have probably written about 1000 different SQL queries in the process (OK, maybe only 500 or so) but a lot. The site is now gaining popularity and although it is on a dedicated server I want to make sure that everything is running smoothly. I have optimised several tables with indexes etc. and have sped up certain tasks, but would like to know if there are any other queries that take too long and should be optimised. Is there a way to log all queries together with the time it took to execute the query? Is this already logged and if so - where is it?
View Replies !
Query Execution Time In PHP Script
I am interested in displaying the query execution time as mysql does from the console, but using php. I've looked everywhere for this and can't find anything. Does anyone know how to display this information to the browser via php? The information must be there since the console provides it. I checked the php function mysql_info(), but that only shows records, duplicates, deleted, matches, changed, warnings, etc. Didnt see any specific php function for getting execution time.
View Replies !
Time Zone For Query Browser?
My server time is set to CST. phpMYSQLadmin shows CST. The website that the information is stored on is CST. When I do a query from MySQL Browser, it's providing information back in GMT. Does the browser undo timezone? It makes since on a corporate level, so that the time is always being pulled up correctly. So, my question is. If all my servers and sites are all set to CST, do I still need to do queries with a time zone change in it, CST?
View Replies !
TIMESTAMP :: Query By Time And Date
I have a field in my table that holds a timestamp. Below is a pseudo-query to describe what I would like to do. SELECT * FROM `mytable` WHERE `saved_date` <= `6/24/05 10:22:34 AM` `saved_date` is the field holding a TIMESTAMP. If anyone could give me the query that would actually do what I am trying to do above,
View Replies !
Query Execution Time & Mysql_stat
I am using mysql for my shopping site,Performance of site very slow then I used mysql_stat php function to find out how many slow queries,it shows 20. Now my question is any function in mysql(or)PHP to show 20 slow queries.
View Replies !
Average Query Execute Time???
I have a query taking about 4 sec that gets only 18 records.. That's not good. What can I do to speen up my Query? <CFQUERY DATASOURCE="#datasource#" NAME="qry_job_search"> SELECT DISTINCT (t1.JobPostId), t1.Actionlkp, t1.Date, tbl_job_posting.*, tbl_job_post_requirements.*, tbl_employer_info.*, tbl_employer_url.*, tblkp_employment_type.*, tblkp_education_exp_lvl.*, tblkp_salary_range.*, tblkp_employment_exp_lvl.*, tbl_job_post_location.*, tbl_job_post_job_categories.JobCat, tblkp_countries.*, tblkp_prefectures.* FROM tbl_job_post_history AS t1 JOIN tbl_job_post_location ON tbl_job_post_location.JobPostID = t1.JobPostID JOIN tblkp_countries ON tbl_job_post_location.CC1 = tblkp_countries.CC1 JOIN tblkp_prefectures ON tbl_job_post_location.CC1 = tblkp_prefectures.CC1 AND tbl_job_post_location.PrefectureID = tblkp_prefectures.ADM1 LEFT JOIN tbl_job_post_requirements ON tbl_job_post_requirements.JobPostID = t1.JobPostID LEFT OUTER JOIN tbl_job_post_job_categories ON tbl_job_post_job_categories.JobPostID = t1.JobPostID JOIN tbl_job_posting ON tbl_job_posting.JobPostID = t1.JobPostID JOIN tbl_employer_info ON tbl_employer_info.UserID = tbl_job_posting.UserID LEFT JOIN tbl_employer_url ON tbl_employer_url.EmployerID = tbl_employer_info.EmployerID JOIN tblkp_employment_type ON tblkp_employment_type.ID = tbl_job_posting.EmploymentType JOIN tblkp_education_exp_lvl ON tblkp_education_exp_lvl.ID = tbl_job_posting.EducationExpType JOIN tblkp_salary_range ON tblkp_salary_range.ID = tbl_job_posting.Salarylkp JOIN tblkp_employment_exp_lvl ON tblkp_employment_exp_lvl.ID = tbl_job_posting.EmploymentExpType WHERE t1.JobPostId NOT IN ( SELECT DISTINCT (JobPostId) FROM tbl_job_post_history WHERE Actionlkp =4 AND date = ( SELECT MAX( date ) FROM tbl_job_post_history WHERE JobPostId = t1.JobPostId ) AND JobPostID = t1.JobPostID ) AND HistID = ( SELECT MAX( HistID ) FROM tbl_job_post_history WHERE JobPostID = t1.JobPostId AND Actionlkp =1 ) AND Date >= ( curdate( ) - INTERVAL 30 DAY ) <CFIF SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A EQ 0 AND SESSION.CITY_A EQ ""> AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#' <CFELSEIF (SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A NEQ 0 AND SESSION.CITY_A EQ "") OR FORM.PREFECTURE NEQ 0> AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#' AND tbl_job_post_location.PrefectureID = '#SESSION.PREFECTURE_A#' <CFELSEIF SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A NEQ 0 AND SESSION.CITY_A NEQ ""> AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#' AND tbl_job_post_location.PrefectureID = '#SESSION.PREFECTURE_A#' AND tbl_job_post_location.PostCity LIKE '%#SESSION.CITY_A#%' </CFIF> <CFIF (SESSION.CATEGORY_A NEQ 1) OR FORM.CATEGORY NEQ ""> AND tbl_job_post_job_categories.JobCat = '#SESSION.CATEGORY_A#' </CFIF> <CFIF (SESSION.KEYWORD NEQ "") OR FORM.KEYWORD NEQ ""> AND t1.Actionlkp = 1 AND (tbl_job_posting.JobTitle LIKE '%#session.Keyword#%' OR tbl_job_posting.JobDescription LIKE '%#session.Keyword#%' OR tbl_job_post_requirements.JobRequirements LIKE '%#session.Keyword#%') <CFIF SESSION.KEYWORD NEQ "" AND SESSION.PGV NEQ "brief"> <CFSET SESSION.PGV = "detailed"> </CFIF> </CFIF> <CFIF SESSION.EMP_TYPE_A NEQ 1> AND tbl_job_posting.EmploymentType = '#SESSION.EMP_TYPE_A#' </CFIF> <CFIF SESSION.EXP_LVL_A NEQ 1> AND tbl_job_posting.EmploymentExpType = '#SESSION.EXP_LVL_A#' </CFIF> <CFIF session.SRT EQ "D"> ORDER BY Date DESC <CFELSEIF session.SRT EQ "C"> ORDER BY tbl_employer_info.CompanyName <CFELSEIF session.SRT EQ "L"> <CFIF form.country NEQ 0> <CFIF form.prefecture NEQ 0> <CFIF form.city NEQ 0> ORDER BY tbl_job_post_location.PostCity </CFIF> <CFELSE> ORDER BY tblkp_prefectures.FullNameS, tbl_job_post_location.PostCity </CFIF> <CFELSE> ORDER BY tblkp_countries.CC1, tblkp_prefectures.FullNameS, tbl_job_post_location.PostCity </CFIF> <CFELSEIF session.SRT EQ "T"> ORDER BY tbl_job_posting.JobTitle </CFIF> </CFQUERY> Can anybody see a shortcut to get the same results??
View Replies !
Comparing Dates Y, M, D , Time In Sql Query
ok.. ive finally got my nice little calender setup to output my dates now how do i query my database for example ive got 2 variables $startdate = 2006-1-1 00:00:01 $enddate = 2006-1-1 23:59:59 these represent the 1st and last second of the first of january 2006 i have a cell in my table (table called challenges) ( cell called time) that contains a date in the same format for each entry how do i get all the entries between the start time and the end time.. can i use less than < and more than > as these are not really integar values the column type is datetime but there is also another column of type datetime
View Replies !
Query Takes A Long Time
On my site I have a query that searches through 1,7 million. The php-file with the query takes a bit of time to load. Is it possible to show some sort of progress-bar during this time?
View Replies !
Looking For Query To Get Time Of Last Column Count Increase
Code: id | time | units ------------------------------------ 1 | 2009-01-06 06:52:32 | 50 1 | 2009-01-02 16:45:23 | 50 1 | 2009-01-02 09:12:16 | 40 1 | 2009-01-01 12:06:00 | 35 2 | 2009-01-04 14:52:30 | 259 2 | 2009-01-04 04:52:30 | 258 2 | 2009-01-03 18:00:01 | 258 I need to write a query that will, for each unique id, return the last time there was an increase in the "units" column. So for this data set, I want to see: Code: id | time ------------------------------------ 1 | 2009-01-02 16:45:23 2 | 2009-01-04 14:52:30
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 To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script, my table having the details of uploading time, file name, & uploader name. i need to find the recent uploaded file for all uploaders. i tried with this query, select file_name,uploded_by,MAX(date_time) from upload group by uploded_by; it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.
View Replies !
Effect Of Increased Users On Query Time - Rec Specs
I have just started my first big project and I am having trouble predicting the system specs that will be required. At present during trials the site has around 15 users, although this will go to 350 when it is launched. Most of the pages execution time should not scale with the increase in users but there are a few report pages which calculate and order the information entered by the users. Measuring the page on my development machine (Duron 1.2, 768MB PC133, WinXP, running apache 1.3.22, mysql 4.0.15and php4.3.4)... One page I am currently getting 0.2-0.3 seconds. It uses phplib templates and it executes 9 queries at present, 1 of which I predict will scale. It calculates the mean, standard deviation and total of around 90 measures using results from 15 users. I do have a few nightmare pages for the regular users which execute 150+ queries (mixture of selects and updates). Can anyone predict what will happen with 300+ users? I am thinking max load being 20 regular users logged in with 2 users viewing reports. The machine I am currently looking at is P4 2.4, 512DDR with Linux. Will this be suitable?
View Replies !
Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query: SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact FROM surfacings INNER JOIN (SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider) AS tmp WHERE surfacings.timestamp = tmp.most_recent; to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....
View Replies !
Time Based Reservation System - Only 1 Query Should Succeed
a user can select a time they want and submit the page * The code selects all the bookings for a range of time. * It then counts how many concurrent bookings there are for each hour. * If there's less than the total (4) it inserts another row ( the user's requested booking ) into the booking table. We also notify people if there has been a cancellation This has led to a situation where a few different people are trying to book the same session at the same time, and succeeding! We've ended up with 5 sessions booked In the course of 2 page requests this seems to happen: User1 - submits their booking site selects to see if there's availability ( there is!) User2 - submits their booking site selects to see if there's availability ( there still is!) site inserts User1's booking site inserts User2's booking
View Replies !
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
View Replies !
Minutes + TIME = TIME Function
I need some way to convert minutes (e.g. 80 mins) to SQL TIME (e.g. 01:20:00) in order to add it to another TIME var. Is there any way to do this? I really dont want to convert my whole db. Basically, I want to do: e.g. 80 + 10:00:00 = 11:20:00.
View Replies !
|
TRACKER
