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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Mysql_connect(): Client Does Not Support Authentication Protocol Requested By Server; Consider Upgrading MySQL Client
I am having an awkward problem with my MySQL installation. I have a number of scripts that access the database from a few different web hosting sites. I have no problem accessing the database from the scripts that I host locally. The scripts that are on an outside server accessing the database gives me a mysql_connect() error. Locally I use MySQL 5.0 and PHP 5.0. I am assuming that the 3rd party web host uses an older version of PHP and thinking that may be the problem. Is this the possible problem and if so is there a way to fix it without ruining the scripts that are currently working?
View Replies !
View Related
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 !
View Related
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 Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
MySql Replication And Network Speed
We purchased 2 new HP DL385 servers that will be used for MySQL 5 in a replication environment. Server 1 (Master) is connected gigabit to our Cisco 3550 core switch The Cisco 3550 core switch is connected gigabit over fiber to a Cisco 3548 switch in another building. Server 2 (Slave) is connected to the Cisco 3548 on a 100MB switched port. My question is whether or not the 100MB connection is going to be good enough to do replication, or if I have to invest in getting gigabit all the way to the slave server?
View Replies !
View Related
How To Speed Up MySQL On A Low Resource Server?
I was wondering, are there any recommended ways to speed MySQL up a bit in low resource environments? See, I'm running a small webserver for kicks on an old Compaq Presario with only 16mb RAM (Yes, I'm aware of the 256mb requirement). MySQL is runnin just fine although obviously it is quite slow. What would you do to speed it up if you absolutely had to? (Besides buying another ram stick >_>)
View Replies !
View Related
Indexes To Speed Up A Duplicates Query.
OK, say you're trying to find all the non-exact duplicates in a table, and especially do it with a single query, so that the check can be part of a user interface. Then naturally you're going to have a slow query, because it has to compare every row with every other row. You ought to be able to cut out exactly half of the records in the second table, by just saying: SELECT * FROM TABLE LEFT JOIN users dups ON users.created<dups.created But I can't get MySQL to use the index I have on 'created' (same results for integer-primary key, & a string field). My best guess why not is that (according to the manual) "MySQL will not use an index when ... the use of the index would require MySQL to access more than 30% of the rows in the table" - is this the reason? Another option: say that I thought that my duplicates will always have the same letter in their surname, then I could make an index on the first letter of the surname, and make the join just on *that*. This should make the number of rows needed up to 26 times less, right? But exactly how is this done? MySQL won't use my index on the following query: SELECT * FROM TABLE LEFT JOIN users dups ON SUBSTRING(users.name,0,1)=SUBSTRING(dups.name,0,1)
View Replies !
View Related
Delete And Speed / Safety Issues
I'm in the process of writing a Perl script to delete records out of a database when the records are more than 90 days old. I'm running MySQL version 4.0.12-standard. The db consists of around 620 MyISAM tables with the following structure: CREATE TABLE `ifInErrors_2` ( `id` int(11) NOT NULL default '0', `dtime` datetime NOT NULL default '0000-00-00 00:00:00', `counter` bigint(20) NOT NULL default '0', KEY `ifInErrors_2_idx` (`dtime`) ) TYPE=MyISAM; The tables range in size from 1-2 rows upwards to over 9,200,000 with an average of around 570,000 rows. Updates to this database run constantly (it records information from a program that monitors the routers and switches on my company's WAN) at a rate of around 2600 inserts/minute. I had originally planned to use syntax similar to: DELETE * FROM table_name WHERE `dtime` < [90 days ago] After the DELETE runs, I plan on running MYISAMCHK on the affected table. Then I'll repeat both steps for all of the other tables in turn. Does anyone have any suggestions for alternatives or is there anything I'm missing here? I'm new to this stuff and may be way off base here. If so, please tell me. One last thing. I'm running out of Drive space and am I/O bound - I'm writing this script in case I can't get the RAID array I'm hoping for. Because of the I/O problem, execution time can sometimes be a factor in what I do. Execution speed is a primary concern. If this takes a day or two to run but the application can be running at the same time then there's no problem. But if I have to shut down my application for any appreciable length I time, then I have to find another way of doing this.
View Replies !
View Related
Database Size To Speed Up Queries
I have a database with a millions of records.The main table has 2 varchars (200), 7 Integers and a date field. Queries are done on 1 of the varchar fields. How do I speed up queries? Indexing the varchar fields? Are there any performance tuning tutorials?
View Replies !
View Related
Multiple Databases Speed Increas
Any speed increase associated with using multiple databases rather than a single database for all of one's tables?. I.e. obviously speed is increased with more tables that are smaller. But is speed increased with more databases that are smaller?
View Replies !
View Related
Search Engine :: How To Speed Up Fulltext?
I am running a search engine that uses mySQL fulltext search for search algorithm (after the search being preprocessed a little). Now since my bot indexed already over 200.000 sites the search engine keeps getting slower and slower. Now I checked what is the factor that causes that slower output of search results: It´s obviously the fulltext - search of mySQL. Do there exist options to speeden up that a little?
View Replies !
View Related
'Show Table Status' Speed
if anyone knows which is faster.. "SHOW TABLE STATUS LIKE '[tablename]';" vs. "SELECT COUNT(*) FROM [tablename];" I'm trying to pick between these two queries (in order to get the size of the table), but want the faster of the two.i expect the table to be very large (millions of rows).
View Replies !
View Related
Speed, Table Size, And Index
I am trying to understand if table size impacts query speed on an query thats looking up an index value. Here is an example query: Select * From Holdings Where Holdings.user_id=3 Assuming there are 50 records in this table for user_id 3, will there be a difference in query speed if the whole table is 10,000 rows versus 10 million, or will the only difference be in storage size of the user_id index? Also, if there is a difference, does it only have to do with whether or not the whole table can be cached in memory, or is there more of a fundamental difference in the way the query would operate?
View Replies !
View Related
How To Speed Up Query On Table With 10k+ Entries
a table with 10k entries in the moment. The table will increase by approx. 30k entries each year. I have seen, that the large number of entries has slowed down my queries (from <1 sec to over 4 sec). I tried to add an extra field called 'archived' and set it to '1' if I only need the entry for statistical purposes. But with the where-clausel speed doesn't improve. What can I do to increase speed without having to move 'archived' entries to another table?
View Replies !
View Related
I Need To Speed Up Deletion Of Duplicate Rows
I have developed the following code for finding duplicate rows. I then calculate the average of each column in the duplicate rows and store them into a mysql table. I then delete the duplicate rows in the main table. Then I insert the results from the mysql table into main table. Does anyone know how I can speed this process up. It runs extremely slow. Thanks in advance. Here is my code. $mysql_table = "mysql"; ....
View Replies !
View Related
|
TRACKER
