Improve Speed
I need to improve the speed of my ff sql statement but I do not know HOW! I am using mysql in running this. If the records are below a thousand, I have no problem getting the info quickly. But I tried it on 700,000 records, the feedback took 22 secs.In this sql statement, I am currenly accessing the same table but I have to get the rows of certain conditions multiplied to different numbers.
SELECT COUNT(IF(code='play',1,NULL))*5+count(IF(code like 'ok%',1,NULL))*2.50 + COUNT(IF(code='prev' OR code='prev_here',1,NULL))*15 + COUNT(IF(code='hello' OR code='hi',1,NULL))*15 + COUNT(IF(code='new',1,NULL))* 2.50 as sum from table;
View Complete Forum Thread with Replies
Related Forum Messages:
How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa' While executing these queries from front end VB, It takes above 5 secs with 50 thousand records. How can I improve speed for this kind of queries. Is there any alternation for this command.
View Replies !
Improve Query
I have this query that I use for stepping through records in a table, it selects for me the lowest, previous, next and highest record id refs for the table in question given the current record number. However it returns its query on two rows with repeated (and obsolete) data due to the case statement, I pick out the data I require using mysql_result command in PHP, but it would be much nicer if there was a way to only return a one row result with only the data I require. user_id is the autoinc field in table_t $id is the current record number being viewed PHP SELECTmin(user_id) AS First,max(user_id) AS Last, CASE when sign(user_id - '$id') > 0 then min(user_id) else max(user_id) end AS PrevNextFROM table_tWHERE user_id <> '$id'GROUP BY sign(user_id - '$id')ORDER BY sign(user_id - '$id') for completeness my PHP code for extracting the values i need is PHP $first = mysql_result($result,0,0);$prev = mysql_result($result,0,2);$next = mysql_result($result,1,2);$last = mysql_result($result,1,1);
View Replies !
Improve SELECT Command
I perform a SELECT on my database, but it takes over a minute for every run. I have to run it over 10000 times (with different values in the WHERE), so it takes way too long. A was therefore wondering if I could improve the query speed. Below you find the query. It is based on the ratio between a pixel (pix) vs. the average of its neighbourhood (from geo) in the same table (vgt) based on additional requirements (mgba,mgsc,eco). Code:
View Replies !
Improve The Execution Time
i am new to db2 want to ask questions about the performance of my sql commands for a view based another 3 views the sql commnads are as following: create view b_central_subgroup as select communicator as central_member, project_id as project_id from b_normalized_communicator intersect select initiator as central_member, project_id as project_id from b_normalized_initiator intersect select monospeaker as central_member, project_id as project_id from b_normalized_monospeaker it takes 4.4 seconds to execute this sql. it likes that the time is the summ of the other three views. Can the execution time be reduced through some other methods? If it is possible , then how ?
View Replies !
How To Improve On A Nested Select
I'm working on a simple data import tool, and I need to insert email addresses from table two into table one, if they don't already exist in table one. I figured this was a pretty easy nested select statement, but what I'm doing is getting my site taken off line for exceeding the CPU limit. Here's the SQL I'm using to get the new email addresses: SELECT distinct value, id FROM table_two WHERE name = 'email_address' AND value NOT IN (SELECT DISTINCT subscriber_email FROM table_one) There are about 4600 rows in table one, and 145,000 rows in table two. Does this seem like it would be a burdensome query? I'm not a SQL expert my any means, so is there a better way to go about this? It seemed like a simple one to me. Maybe my web host is just stingy with the CPU time.
View Replies !
Best Way To Improve Performance On Order By?
I hope someone can help me with the following problem. (Note: I will simplify my table structure to the essential) I have two tables, one containing objects and one containing the objects a user has, so basically I have * table_userObjects with PRIMARY KEY idUserObject INDEX ON idObject (not unique) * table_objects with PRIMARY KEY idObject INDEX ON name What I want to optimize is the query which gets the objects for one user and sorts them by name. For example: SELECT * FROM table_userObjects,table_objects WHERE table_userObjects.idObject = table_objects.idObject AND table_userObjects.idUser = 3 ORDER BY table_objects.name LIMIT x,30 The db has around 40000 different objects and the top users have 200000 different items. In this cases it takes around 6 seconds to run the query. If I leave out the orderby no sorting has to be done and the query runs fast as expected. Is there anyway to create an index on table_userObjects, based on the name of the objects from table_objects? Or some other way to speed up this query?
View Replies !
Indexes To Improve Performance
I am looking to add indexes to my database to try and improve performance. Now I heard someone say once that you should add an index to any column that you are planning to filter using in the where part of your statements. So my question is this, in below is an example i have a table that I use to store reports that are generated by the system (i haven't chosen this especially but it has the core element that i user everywhere else). These core elements and columns that i regally in a where clause have '*' next to them. As such if i was following the advice of were to add indexes, i would probably add one to each of these columns. Then there are the other three that have '+', which occasionally i conduct a '%%' (wildcard) search on to help me find a cretin report. given this, plus the index on the primary key almost every table would have an index on it. To me this seems a bit much. So how many is too many?
View Replies !
How Do You Improve The Order By In Queries?
I read somewhere about mysql having to scan the table twice or something with "order by something" this was on mysql.com (as far as I remember) it said something about having it only have to scan it once but it didn't explain how to do it I really want to know, because a 500k row table of mine with a few indexes that cut it down to about 90k per topic is lagging more than I want (okay, so 1.2 seconds average page load on a 8mb connection isn't too bad, but I want to fix this order by stuff and maybe make it 0.9 or 1.0 :P)
View Replies !
Index To Improve Queries With AVG()
Is there any way to create some sort of index that will improve the performance of queries which use the AVG() function. An EXPLAIN statement on the query indicates that no index is being used. There are indexes on the ratings table for ID and ratings but they don't seem to be getting used EXPLAIN SELECT u.username AS username, ur.users_ID AS user_ID, IFNULL((AVG(r.rating))*(COUNT(ur.ratings_ID)),0) AS overall_score FROM ratings AS r, user_ratings AS ur, users AS u WHERE r.ID=ur.ratings_ID AND ur.active='yes' AND u.ID=ur.users_ID AND u.active='yes' GROUP BY ur.users_ID ORDER BY overall_score DESC LIMIT 0,40 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE r ALL PRIMARY 5 Using temporary; Using filesort 1 SIMPLE ur ref rating,users_ID_2,active rating 4 v2.r.ID 239 Using where 1 SIMPLE u eq_ref PRIMARY,active PRIMARY 4 v2.ur.users_ID 1 Using where
View Replies !
How To Set An Index On Table To Improve The Performance
i've got a table with about 500 mio records. there are 3 col. decimal,decimal,int. my aim is to find data which fits best to the two decimal col. in which way should i set the index for the best performance? at the moment my query needs sth about 5min and it shouldn't need longer than 10 sec. is it possible to improve the performance only by using the correct index.
View Replies !
Limit Records To Improve Performance
I'm facing a performance issue, I'm using JDBC to read rows on a huge mySQL database 1.5 Million rows. The programme basically read rows by sample of 1000 rows. select * from <tbl_name> LIMIT i, i+1000 and print result in a text file. This takes 5 minutes for the first 500000 rows, 10 min for the following 500000 and 18 for the rest. Which seems to me very slow just for reading rows? I wonder 2 things: 1- is this normal for mySQL to take this time? 2- if not, is using LIMIT this way in the SQL would have an effect on mySQL performance? 2- how can I improve this performance. If you think of anything that can improve the performance on a select * from , please let me know.
View Replies !
Speed Up This Query
I have a table with just userid,indexid,and imgid and I use this: SELECT count(userid), indexid, count( imgid ) FROM `photoindex` GROUP BY `indexid`; the table has 2 million rows and it says it take 2sec to finish but really, it takes 15 sec after sending the query I think the lag time is due to mysql reading the entire table into a temporary table in order to do the GROUP BY.can anyone help me reduce this query time? is there a SQL setting to increase the memory to speed up this type of query?
View Replies !
Retrieval Speed
I am in the phase of designing a large database which potentially could go up to 50,000,000 row. I wonder with that kind of number, how would the it affect the retrieval speed ? Let say I want to get 1 record out of this 50,000,000 I am holding in the mySQL table
View Replies !
Speed Of Join
I am having a heck of time trying to get this query to run decently fast. This query took 2 hours to run. Originally I was joining two tables with 200,000 and never stayed around long enough to see it finish. So, I wrote a script that dumped the results of one query into a holding table. That holding table has 3000 records. I can't figure out why this query is running so slow. Any ideas how to make it faster? If so, how much speed can I expect for an increase? SELECT count(*) as bednum, needs_services.provide_start_date,needs_services.provide_end_date, shelter_bedlist_id, needs_services.inactive FROM holder LEFT JOIN needs_services ON needs_services.need_id = holder.uid AND needs_services.inactive != 1 GROUP BY needs_services.shelter_bedlist_id;
View Replies !
Speed Up MySQL
I'm currently running mySQL 5.0 community server on Intel core 2 duo with 4GB and Vista business. I have two database, each of around 10million rows, 5GB in size. Whenever I perform operation on these, it takes a long time (>20min). Is there anyway to speed up the process?
View Replies !
Speed Up Insert
I have table in mysql database with hundreds of selects per second.Once in 15 minutes there is insert to this table.The problem is that this single insert take a lot of time about 1 sec.I tryied to play with mysql variables but without succses. I did test and inserted to table with same structure without selects and it take 0.01 sec.
View Replies !
Performance (speed)
I have implemented a simple web crawler which uses a mySQL database to save a list of URLs to crawl. Obviously I dont wish to crawl the same URL twice so before adding a URL to the database I search the database first to ensure it hasn't already been added. When I add URLs to the database it is usually done it batches of 150 URLs, this is the process I use to search for an existing URL: I use a PreparedStatement called 'findUrlStatement' which consists of: SELECT Url FROM urlTable WHERE Url = ? Then I use this code to determine if a URL is already in he database: findUrlStatement.setString(1, url); ResultSet urlFound = findUrlStatement.executeQuery(); if (!urlFound.next()) { // The URL is NEW, so add it to the database } As you can see the above routine is repeated for each of the 150 URLs. This typically takes anything from 4-8 seconds to complete.
View Replies !
Update Speed
I have a master_parts table with around 700k records, and is expected to grow up to 10 million, that is why I have decided to switch to MySQL. Now, I'm having trouble using an update statement. I tried to replicate this in access 2007 and it only took like 10 seconds for about 1000 records in the update_category table to finish. There can be as many as half a million records to be updated from the update_category table.....
View Replies !
Speed From Second Machine Bad
I am new to MySQL (mysql-4.1.7-win) with MySql ODBC (MyODBC-3.51.10-x86-win-32bit) and (vb 6 with service pack 5 or Access 2003) to connect via adbc to MySQL. My seccond machine are terribly slow and would like to know if there are any suggestions.
View Replies !
Speed Issues
I have 15,000,000 bank records that are querried against 60,000 census records. There is a primary index on the census table(concatonation of state/msa and tract) that corresponds to an index on the bank table(not unique on the bank table). I need these indexs to perform timely INNER JOIN querries. NOTE - all querries that I describe are make temporary tables. They have to be for my app - unless there is an alternate way similar to make tables that I can use. Maybe w/ views? The proscess works if I filter the bank table down to the state and county levels. If I just filter at the state level the querries drag - Illinois can be 5-10 minutes. I tried to add indexs on the STATE in both the bank and census table but it didn't seem to affect the peformance. Is Mysql not made to handle 10s of millions of records? Is 10 minutes not long on this query? Am I properly indexing? If I remove the census table from the equation and just do a straight WHERE things still slog at a slow pace
View Replies !
Speed To Lookup Value
I am considering having a database of 8,000 IP addresses of search engine robots and other bots. If I put this in a MySQL database, how long would it take to lookup that IP address in the database to check whether it is a surfer or bot on the site? Any ideas? I would only be querying the IP address against the 8,000 values in the MySQL database.
View Replies !
Speed Of Listings
I am having problems with the speed of MY MYSQL database when delivring listings from a real estate agent management system developed using Perl and a MYSQL Database, we have tried Modperl but it mucks up too many other things and we have had to disable it. When a user goes to a website such as www.propertypoint.com.au and performs a search for property listigns the results come up very quickly, but when a user clicks on a listing to view a full property page, it sometimes takes 10,15,20 and even 30 seconds for it to load.
View Replies !
MySQL Speed
I'm using a mySQL database to store all user information, as well as all the images (in blobs.) the site is plenty fast currently, but i'm worried that as it grows, all the database access will slow down a lot. does anyone have any tips or information on speeding all of this up? would caching images on disk be faster than pulling them out of the database? i guess i'm really just asking for general mySQL optimization techniques. anyone?
View Replies !
Speed This Query Up?
I have a simple query I am running, but it is taking 6 seconds to run. Im not sure what I can do to speed this up. Am I simply SOL or am I not doing something I could be?
View Replies !
Speed Of Query
I was on the PHP forum and they sent me here. I am quite new to working with large databases (approx 1 million records per table) and am having problems with the speed of viewing the data. I have a page that requests all users from a certain department and displays their names etc (this uses a simple 'SELECT first_name etc FROM staff WHERE department = 3' style query). There are around 800,000 staff in the table and so this query can take a long time to run (sometimes only to return 1 record). Is there any way of speeding up this query? Someone mentioned that creating an index on the department field may speed things up but they didn't seem sure, and I don't know how to do this so would only spend time learning if it might make a large difference! Large websites must deal with tables far larger than this and I was wondering how.
View Replies !
Can You See Any Way To Speed This Query Up A Bit?
SELECT SQL_CALC_FOUND_ROWS title, content, URL, id, (MATCH(URL) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 1.5) + (MATCH(title) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 0.8) + (MATCH(content) AGAINST ('$searchQuery' IN BOOLEAN MODE) * 0.5) AS score FROM $searchType WHERE MATCH(title, URL, content) AGAINST ('$searchQuery' IN BOOLEAN MODE) AND content != '' ORDER BY score DESC LIMIT $limit OFFSET $offset
View Replies !
Speed Up A Group By
would like to know whether CHAR(10) AUTO_INCREMENT will work like INT(10) AUTO_INCREMENT? or is there another field definition that will allow me to implement alphabetical increment?
View Replies !
Any Way To Speed Up Select Distinct
I have a table with a varchar(255) column, it has 111,000 rows. When I do a select distinct on that column it takes 16 seconds and returns about 25 distinct values, I'd like it to take much less time. I tried creating an index but the explain on the query shows that it isn't using an index. Is there any way to speed this up or should I just maintain another table with the distinct values myself?
View Replies !
Minimum Speed For Mysql
We are trying to use mysql in some very high speed applications. To get an Idea of how fast it could run, we did a test query on a table with one row only (and also a zero row table if that matters). The query was a simple "select * from table". The number that came back was 300-400 microseconds. A similar test directly to memory came back in the nanosecond range (we did not do any locking, etc, it was just a simple insert or get of an element in a one row array). If this number is true, it is too slow for us. I am confused though about why MySQL is so slow. We have also tested using an embedded version of mysql, and the time did not improve. We also made the table a heap table, and put it in memory, but the time still was the same. It seems like this is a lower limit to the time. If mysql is not sufficient for these speeds, could someone suggest another database or library that would work? We will have to read and write from different processes, so it must have locking. Shared memory is not a great solution because we are worried that if we write our locking by hand, the code will be buggy and freeze up.
View Replies !
Quick Ones To Speed Up Access
------=_NextPart_000_0032_01C35FC2.62884DE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am querying from a table containing more than 40,000 records. Earlier = when the records were 10,000 it was taking 9 sec and now after 1 year = and 40,000 records its taking 30 sec. Code is the same.=20 I am pretty sure that it has something to do with database only. No body = can think of spending 30 secs for retrieving values from tables unless = it's very huge in the sense of millions of records. =20 I don know whether I have to modify my database or do some sort of = restructuring or reindexing so as to make it fast enough. Is there some methods or optmization which can be applied to this = database which hasn't been touched since design to enhance the spped.=20 If there are some quick ones but valued alottttttt please let me know.
View Replies !
Named Pipe Vs TCP/IP Speed
I just noticed this in the manual yesterday: http://www.mysql.com/doc/en/Windows_running.html "MySQL supports TCP/IP on all Windows platforms. The mysqld-nt and mysql-max-nt servers support named pipes on NT, 2000, and XP. The default is to use TCP/IP regardless of the platform, because named pipes are actually *slower* than TCP/IP ..." This surprised me! I have been connecting with named pipes (and telling others to do the same) on Win2k since I discovered them, because my queries (and time to connect) are most definitely *faster*. The client/server communication is about 25-30% slower using TCP/IP. Example: a simple SELECT COUNT(*) FROM table takes 0.3ms with TCP/IP compared to only 0.16ms with named pipes. Or, on a more complicated query, 2ms vs 1.5. Speed differences seem comparable to Unix sockets vs TCP/IP on *nix. Should the manual info be the other way around? If not, then why am I seeing it that way? ;-) Any comments?
View Replies !
Speed Up BLOB Search
I have a database with about 60.000 rows that contain in total 500MB in one TEXT colum. Now I need do so some searches in the TEXT fields. I use the following code: SELECT * WHERE DATE_FORMAT(pubdate, '%Y%m%d')<='20041111' AND (tekst LIKE '%america%') GROUP BY article_id ORDER BY DATE_FORMAT(pubdate, '%Y%m%d%%H%i%S') DESC, bewerkt DESC Is the (tekst LIKE '%america%') part the fastest way to search in a TEXT colom? Is there a way to speed this up? For example with indexing?
View Replies !
Foreign Keys And Speed
I am working with another developer who doesn't not like to use foreign keys as he says they slow down the speed of the database - says it is up to the developers to keep the integrity of the data. I was wondering how true this is in terms of the foreign keys. If it is true, how much of hit is it? How big does the database and usage have to become before this will have an effect on speed?
View Replies !
Select Speed And Index Vs FK
I have two tables Books and UserBooks Books: id,int PK value, varchar type,tinyint UserBooks: id, int PK userid int bookid int type int My problem is that I want to see the most "possessed" book. SELECT value,count(*) as cnt FROM Books,UserBooks where books.id=bookid group by Books.id order by cnt desc; I have a Index on UserBooks on userid,bookid and type and an index on value on Books. I created the last index because the query is very slow (300.000 books and 800.000 UserBooks rows) and I was wondering if I have to modify userid and bookid and set them as foreign key..maybe it's faster. Otherwise I have no idea what to do. The query is very slow. I plan to add AND SUBSTR(value,1,1)='X' (where X is every letter of the alphabet) and I think it will get even worse than now.
View Replies !
Speed Up Geonames Search
I search the nearest places from my lat, long coordinates. The query is very slow, I use this in PHP: $geo = mysql_query(' SELECT `name`, `ansiname`, DEGREES( ACOS( (SIN(RADIANS(''.$sor['szelesseg'].'')) * SIN(RADIANS(latitude)) ) + ( COS(RADIANS(''.$sor['szelesseg'].'')) * COS(RADIANS(latitude)) * COS(RADIANS(''.$sor['hosszusag'].'' - longitude)) ) ) *60 * 1.1515 * 1.61 ) AS distance_km FROM geonames WHERE `feature_class` = 'P' and `population` > 2000 ORDER BY `distance_km` ASC LIMIT 1 '); I read one solution in other forum: This search apply rough lan long box criteria to the search... http://forum.geonames.org/gforum/posts/list/0/522.page#2492 I try adapt this solution, but I can't.
View Replies !
Client Speed Issue
I have tried various client apps on both Mac OSX and on Windows XP. Clients tried (Windows) MySQL Administrator MySQL Query Browser http://isql.sourceforge.net Clients tried (OSX) MySQL Administrator http://isql.sourceforge.net command line mysql On Mac OSX I get an instant response from the server But on the Window XP machine it takes about 10 seconds to respond to any query. The query info passed back indicates that it took 0.02 secs for the query on the server. Server is iMac running OSX Mac Client is G5 running OSX Windows client is running Windows XP connection is over tcp network is a 10/100 switch Is there a tcp issue on Windows machines?
View Replies !
How To Speed Up Select Query
I have a very big table >2GB. My query is as follows: Select JDay, cda_lake.Cell_Num,NH4, NOx, TP, DO, pH from cda_lake, site_cells where Site_Num =1 and site_cells.Cell_Num = cda_lake.Cell_Num and truncate(JDay,0)=152 order by JDay,Cell_Num; It took more 1 min to get 20504 records. Can you tell me what's wrong?
View Replies !
Speed Vs Field Type/value
I have an integer field in a table or view, and that field is used in the WHERE clause of a SELECT statement. Will the query take longer if the field values are in the upper range of the max vs. the lower range ( 200000000 vs 1, 2, 3), or is a 32 bit integer queried at the same speed regardless of the value?
View Replies !
On What Depends Speed Of The Query?
I didnt have too much in common with large databases and i wonder what kind of query is suposed to be complicated. i mean in terms of performance. are there the golden rules like: when you have more then 5 where conditions use union or something? do you think this query could be written in a "faster" way?
View Replies !
Query Vs Logic Speed
I have a website which uses some fairly large tables ( > 300,000 rows ). As you can imagine accessing these tables can often be very slow. I am attempting speed up my sql and was wondering if, using php, it is faster to perform an sql query or approximately 10 lines of logic? What I am trying to do is, instead of querying the table thousands of times and performing my calculation on each small chunk, I would like to try to get the whole table at once, sort it by the ID's and just iterate over it and perform the calculations I need. Would this speed things up? how much if any? and is it worth the effort?
View Replies !
MySQL Speed Test !!
let me explain, I have a Database with 30,000 rows having a single field of length 100 bytes My current database driver takes 0.339067955017 seconds to search through the entire database and find an entry at last record.
View Replies !
|
TRACKER
