MYSQL Access Speeds
When developing complex systems using PHP and MySQL what technqieus do you suggest to quicken up the access speeds to MySQL?
Here are a few that I already know and just wondered if there are any missing?
INDEXES
Only search relevant columns not *
Have good joins and where clauses
Use distinct whenever applicable
View Complete Forum Thread with Replies
Related Forum Messages:
Slow Write Speeds
Here is what i have IBM x226 - 8GB ram - 2x 3.4 Ghz Xeon (single core) processors - RAID 5E (6x300GB disks) Suse Linux 9.0; MySQL 5.0.13 So, I am migrating from SQL server 2000 on a much smaller Dell PowerEdge (2gb ram, 2 Xeon 3.0 Ghz, Raid 5 over 5x74GB). I am exeriencing very slow write times. For instance, there is one table on both servers that has approx 9mil rows...here is a basic test: Update loan set test_col = 1; Platform Time IBM/Linux/MySQL 5 9:45 Dell/Win/SQL server 1:37 The tests were run during very light server activity and I tried them multiple times. Our IT department is fairly insistant that the IBM server is running per spec...obviously something is not right. I have changed some of the server variables with no improvement.
View Replies !
Foreign Key Speeds Up INNER JOIN Query?
I've read the foreign key topic on the mysql manual but it says nothing if foreign keys optimize the queries with INNER JOIN on the foreign key. I've read that indexes in general speed it up, but is it valid too for foreign index? I am often linking a medium sized table to a huge sized table via INNER JOIN and I wonder if setting up foreign keys would speed up the INNER JOIN. And is this effect valid to all engines? If not, to which ones is it valid?
View Replies !
Can Access Remotely, Cna't Access Locally (seems Backwards)
I have a database that I am trying to connect to a schema called lportal4_1_2 in mysql 4.1.13a (provided by my hosting company) runnning in fedora. I am trying to get a container to connect to it and was having problems. Since I didn't want to pollute the problem with the container, I wrote a small (hack) program that does the connection. Here is the problem: I can connect from my desktop (winxp) using a Java command line application, Mysql Administrator and mysql query browser and everything works fine. When I run the same Java command line app (using the exact same driver) I get the Access denied for user error that follows: ---- java.sql.SQLException: Access denied for user 'lportal'@'inkbrains.com' (using password: YES) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798) at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)...........................
View Replies !
Can't Access Created DB's Via PHP, Though Can Access Them Via PhpMyAdmin
I can create databases via mysql-shell and edit them. These then do show in phpMyAdmin and are editable. BUT, if I try to access these db's via a normal php-script (i.e. $ergebnis=mysql__list_dbs($server)), it only shows me one database, the test-db. (To show them, I use a for-loop which counts the elements in $ergebnis from 0 to the number smaller than mysql_num_rows($server) and then prints the string.)
View Replies !
Question Regarding MySQL Interface Options And MySQL Vs MS Access
First, there are many applications available for interacting with MySQL. One of the things that frustrated me at first was the CLI. I know that it's the foundation and understanding syntax is critical to making it all work in the end, but when dealing with large datasets, I feel that a GUI makes life easier at times. So, I started out with phpMyAdmin, seems to be 'the' geek-preferred application. I also ran into a few shareware ones here and there as well as Navicat, and I recently learned that there are 2 apps from MySQL.com for administrating the DB and creating queries. In your experience, what seems to be the best MySQL interface application? At the moment I couldn't say *exactly* what I'm looking for, but more-or-less something that will ease the headache of creating queries and managing users, tables, etc. Question #2: I have been hired to be a Web Developer, Network Admin, and Database Administrator all-in-one. We have are a retail company selling peuter goods and have a product line-up of about 100 items. The President wants a robust database to deal with everything. Right now we use MS Excel for our needs. He says it's becoming cumbersome and wants to move on. I suggested MS Access because that's exactly what it's built for, companies that have been running in Excel and want more robust control over what they're doing. But the president keeps hearing this 'MySQL' buzzword and wants a MySQL database. Fine with me, I just know Access better, that's all. I've been cramming PHP & MySQL down as fast as I can and am starting to feel more familiar with both technologies. At the moment, I feel MySQL to be more of a headache (my fault I'm sure, I just don't know it yet) But For Example: To properly normalize a DB, I sometimes have to break a table into 3 tables. The first and second are no problem, but the third 'association' table is annoying (at least doing it CLI or phpMyAdmin). See, Access, you just make 2 tables and graphically set associations very easily. So, from what you know of MySQL, do you think it would be still superior to Access seeing as we want to integrate it into osCommerce eventually and possibly some of their user contributions?
View Replies !
Mysql->MS Access
How do I to move data on an external server (over the Internet) in mySql to MS Access on my office computer. I would like to remove the data from the Net and store it on my computer.
View Replies !
Access And MySQL
I can link MS Access (front) with MySQL (back end). However, when I add a new record, the previous added record is signed as Deleted record (view in Access) even I can view this record in MySQL....
View Replies !
MS Access => MySQL
A friend of mine mailed me a access database (.mdb), but I only use Linux (RH 9.0). Is there anyway that I can open the database in mysql?
View Replies !
MS Access -> MySQL
I am converting some queries from stored procedures in MS Access to MySQL, and have hit my first problem. The Access query references another stored procedure, which is where the problem lies. I have the following table data: (apologies if the columns aren't aligned properly... it looks ok in a fixed-width font) ......
View Replies !
MySQL, Access
i have a central mysql database that is accesed by multiple clients running MS Access with ODBC. This works fine if Access has a permanent connection to mysql. But now, i want MS Access to operate offline (e.g. on notebook in the field). I tried to create a local replica of the ODBC database, but the local replica still requires an ODBC connection to the running mysql server. I do not know why. What is teh best way to realize synchronization between a local and an odbc database using MS Access ?
View Replies !
Access Mysql
I've been having weird issues with one of my tables - some records are disappearing - how can I check if my db has been violated or accessed by someone else
View Replies !
MS Access Db To MySQL Db
I tried converting a MS Access table (made from an Excel spreadsheet) to a MySQL table in the built-in 'mysql' database, but when I look at the table in MySQL, it only shows the first 1000 rows of my table. There are supposed to be 2800+ rows. I can't believe I'm exceeding any memory issues here - is there a better way to turn my Excel spreadsheet into a MySQL table without losing rows? Or is there an easy way to just add the rows to the table in mysql? I tried the 'insert' button, but it places the new row at the top and I need to tack it all at the bottom.
View Replies !
MS Access SQL To MYSQL
here's the situation... i used to use access but now i use php with mysql... i need to 'convert' this to php... its too difficult for me... here it is: SELECT Emissor, Base, Sum base*retorno+kms*0,3 as total, Sum base as total_base, Sum kms as total_kms, Sum kms*0,3 as value_kms, count NGuia as number, sum Tespera as Total_espera FROM baterist_guias GROUP BY Emissor, Tservico; and by the way can you tell me how to retrieve the values that are calculated in this command? like total_kms or total_base...
View Replies !
Mysql Access
My computer crashed yesterday (of course I backed my data) So I'm in the process of getting things back up and running. The problem I'm having is with mysql. My site is currently offline. When I try to access phpmyadmin I get wrong username/password. When I access a survey program that I wrap in mambo I get: Error Error connecting to database: Client does not support authentication protocol requested by server; consider upgrading MySQL client How do I get access to change my username and passwd in mysql?
View Replies !
MS Access And MySql
I´m working in an application using Access as FE and MySql as BE. When I add some registers to the linked table, OK. Problem: When I try to add a lot of registers (700.000!) it does´nt work and the msg is that the computer does´nt have memory resources to do the job.
View Replies !
MS Access To MySQL
I am currently building a website and have been storing my data in an MS Access database. However in the forum I have built the page is struggling to open more than 5 records (posts), and can take around 10 - 20 seconds to open a thread with 15 posts. Ive heard that MySQL is much faster than MS Access and should resolve my problem, is this true? If so how can I go about setting up a secure MySQL database. I'm totally new to MySQL so if its too long to explain do you know any useful tutorials I can use?
View Replies !
Cpanel Can Access But For Others Access Denied
I can access my database through Cpanel's GUI with no prob, but when I tried using MySQL-front in my comp, it always give me an error access denied. I supplied correct username and pass but still no luck. and How do I configure MySQL-front to connect to my webserver's mysql db?
View Replies !
Porting Access To MySQL
I have to port a MS Access DB to anotherdatabase engine and I would like to use MySQL because I am familiar with it and is easy to integrate with the web and php what is the ned point of my 'quest'. However there are paramter queries in the access database and I would really like to keep the access database as a backup as long as I'm in the progress of moving/porting. I have taken a look at the ODBC connector (3.51) and have tried a few things. There is only one problem which gives me a headache. Is there a way to support parameterized access queries in the MS Access frontend with the ODBC connector and the MySQL backend?
View Replies !
Mysql.exe Access Denied
Decided to update my desktop with latest MySQL Server 4.1.10a and connector 3.1.6. Un-installed MySQL Server 4.0.21. Now mysql.exe is refusing to start with error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Get same error when starting mysql.exe w/o user argument, except default user is ODBC rather than root.
View Replies !
MS Access And MySQL Databases
I have an Access database that is used for a station computer. Now I want to update this database for a small network for about 4 computers (to share this database). I know that I can link an Access databse to a MySQL server. My question is: It is possible to use one of those for computers as a MySQL server then 4 computers will use 4 copies of this Access application to connect to the MySQL server to share data.
View Replies !
Switching From Ms Access To MySql
1. Is Mysql secure than MsAccess (there are softwares avilable to retrieve Ms Office Password) 2. Which 3rd Party tool Would You recommend for Administration,Query creation.... Etc 3.Any tools for setup Mysql database to ship with ApplicationProgram (Access need not be installed in a Windows machine) 4.Any known problems in a VB6/VB.Net - Mysql Combinations
View Replies !
Access/jet To SQLServer Or MySQL
We have developed an Access/Jet database (2000, XP & 2003 versions) that has been used by clients all around the world for several years and is extremely robust with 55 tables, 172 hard stored queries (plus about the same in VBA SQL code), 60 forms and about 18,000 lines of code. The biggest advantage with Access is that it doesn't cost the client anything for if they don't have Access already then we provide the Runtime version which makes our application extremely cost effective for the client. The most concurrent users recorded has been 23 without any problems and in 5 years we have only ever had 1 corruption when a server fell over. We now need to expand the concurrent user abilities with the most being say around 175 at the worst possible time with the objective being to allow employees in an organisation to enter a record (for eg. logging an IT Help Desk Call themselves rather then contacting the Help Desk and getting them to log the call). We know that which ever way we go there will be a massive learning curve and there WILL be an impact to the cost to the client as only some clients have MS SQL Server. Strategically we POSSIBLY may down the track be moving away from Access and to ASP.NET as the front end (or both) but at the moment we believe we have 2 options - either MS SQL Server or MySQL for the backend. I must stress that the most important factors are cost to the client, ease of self installation, size of learning curve for us and the ease of future enhancement development and support to the client.
View Replies !
Importing MySQL In MS Access
I'm building a shell script that store information in a MySQL 5.0 database. One of the data I store in the table is a text file. I use a BLOB type for this text data. If I try to import it in MS Access 2002 the BLOB column is converted to a OLE type, but when I try to open it in access I get this error: "A problem occurred while Miscrosoft Access was communicating with the OLE server or ActiveX Control. Close the OLE server and restart it outside of Microsoft Access. Then try the original operation again in Microsoft Access." I know that it's because MS access doesn't know what type of object it is and with which program it should open it. But I don't know how I can tell access the type of object it is. Should I create a module to handle these objects? I'm new to access databases so I don't know where I should start. If someone has any tips or codes I should use please le me know. Should I use another type instead of BLOB?
View Replies !
MS Access, MySQL & MyODBC .....
I am using MySQL ODBC 3.51 Driver, with MS Access 2003 and MySQL 4.1.11 standard log. I created my tables in MS Access, then exported them via ODBC to an externally hosted MySQL database (fasthosts) . I then import-linked them back into my MS Access database. Before I import linked them back, I edited the tables in MySQL so that the PK & Autonumber was set and added a timestamp field. The problem I seem to have is that when I use the Timestamp field in MySQL, I cannot seem to update current records on my Access forms without getting the error 'Reserved Error (-7776)'. If I remove the Timestamp field, the error goes away, but then I get the usual #Deleted problem when adding records etc. I know the timestamp is required, but I cannot seem to get arround this unknown error ?
View Replies !
Connecting To MYSQL Via MS Access
I have recently decided to start using MYSQL instead of MS Access 2003. After reading through numerous articles i have learnt that i can use access as a GUI for making changes etc to the MYSQL databases. My problem is that i am unable to set this up correctly. give me a set by set guide on how to do this or give me a link to a site or post that outlines how to do this. i have searched google without much luck at this point. I have attempted to use the odbc method but i do not know what to browse for when the wizard prompts me to browse. i have never use this method before.
View Replies !
Mysql Access Through ODBC
I have a Mysql table which has over 40000 records. If i want to access from VB using ODBC connection. will that be good choice related to performance wise. or any other options there.
View Replies !
MySQL Access Denied
Im running Mandrake Linux 9.1 on a Laptop I recently bought, and its purpose its a PHP development machine, and I want to use a MySQL Backend. I think when I was installing MySQL, something got corrupt, and I cannot do anything, it says access denied(And no password was ever set, and I've tried all the "--skip-grant-tables" and all that stuff). Ive tried installing and uninstalling the RPM' packages, but a database I created(called "test") is there after i unistall and reinstall. This shows me something somewhere isnt being taken out, and if it did get taken out, it could solve my problems. Just an example: [kevin@localhost kevin]$ mysqladmin ping mysqld is alive [kevin@localhost kevin]$ mysqladmin create test1 mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'test1''
View Replies !
Mysql: Access Denied
I can only access mysql through the root by typing mysql -u root -p. But when I use any other user to connect to mysql I get the access denied error message. Mysql is installed on windows xp. I looked up information online to solve this problem and saw that there are some scripts that i may be able to run in order to fix the problem. But I am not sure how to handle those scripts since they are only made for unix systems. I tried to connect to the server using PHP, but i cant connect as well. The way i am create users is by inserting a user in the user table.. or by granting privileges to a random user. Is there any other way of creating users? The way I am checking whether a user account is working is by typing mysql -u username -ppassword. Is there any other way of doing this.. Anyone know why access denied but the users i created do show in the user table.
View Replies !
Access Module Into MySQL
I was trying to do this in Access, but now that I'm trying to learn MySQL, I have bulk table imports; one field (LongClass) has data that looks like this. ALW 25000N1X, ALW 5000N3X , ALW 15235s. What I've wanted to do is find a manner to remove those center numerics so that the LongClass is reduced to ALW N1X, ALW N3X and ALW s respectively upon it's import/conversion into the standard data table. As you can see, the length of that numeric is variable. The kind person in the past was able to give me this code for a module in Access: Expand|Select|Wrap|Line Numbers
View Replies !
Access MySQL In Python
Here are the softwares that I use: MySQL : 3.23.54 Python : 2.3.4 GCC : 3.2.2 MySQL-python-0.9.1-6.i386.rpm when I type in "import _mysql" in a python shell,an error occurs and I have spent a lot of time handling with it but it didn't make any sense.The message is as follows: [root@Qiuping backup]# python Python 2.3.4 (#1, Mar 30 2006, 14:18:07) [GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5)] on linux2 >>> import _mysql Traceback (most recent call last): File "<stdin>", line 1, in ? ImportError: /usr/local/lib/python2.3/site-packages/_mysql.so: undefined symbol: mysql_set_character_set
View Replies !
Importing Access To MySQL
I have a site that I want to change over from ASP / Access to a PHP / MySQL platform. My only issue is that the client uploads a new version of the Access DB every few weeks. I don't think they'd be able to upload it in any other format... so I'd need a way to import the .mdb file into MySQL.
View Replies !
Migrating From Access To MySQL
I have an Access database that has served its purpose well but will not cut it in the future. Does anyone have any good suggestions or tutorials on MySQL? I need to know how to create MySQL tables and how to migrate my data up to my web server/host.And is all of this really difficult?
View Replies !
MySQL, ODBC And MS Access
I am working with MS Access and mySQL via ODBC. The developer of the mySQL db as used "text" fields in several tables instead of Char/Varchar fields. This poses a problem in that the fields only contain 8 or 10 char and I cannot use them in MS access queries to Join tables since the come across as MEMO fields inthe ODBC linked table.. The developer stated to me that the ODBC driver sometimes does this to Char/Varchar fields. I have worked with ODBC drivers to other DB's including Postgres and Firebird and have never had this problem. I am going to recommend to my client that he have the developer create an empty datbase whereby he replaces the collumns defined as "text" with CHAR (x) of appropriate lengths. Fortunately the other tables in sysem are using integer fields for the keys. Is there a tool that can pump data from one mySQL database to another and deal with the fact that I am moving data from text to a char/varchar field?
View Replies !
MySQL Remote Access
I want to be able to access my MySQL database that I installed on linux, from my Windows 2000 machine. I will be using the database from various programs such as VB 6.0, MS Access, etc. I have installed MyODBC on both my linux machine and my Windows 2000 machine. I want to be able to access the database through this MyODBC connection on my Windows machine but I'm not sure how to go about setting this up on both machines. I'm am looking for some advice as to how to set this up or if anyone can point me to some good information on the web
View Replies !
Converting Access To Mysql
I'm converting a MS Access database to MySQL and encountered the following column names and I'm not sure if it's okay or better not to use those names: CODE, MASTER (in lowercase by the way) MASTER made me doubt since there's the whole replicatin situation with master and slave, so I'm not sure if I can end up with problems naming a column 'master'.
View Replies !
Switching From Access To MySQL
On the "programming part" am i going to have to do my queries differently? Updates and all that differently? I recall people asking my database, then saying "I dont know how access does that" when having problem programming something... i have over 100 pages and 30 tables and don't really want to change but heard i should have done it about 100 pages and 30 tables ago :P So after I switch am i doing to have to change my code too?
View Replies !
Migrating Asp/access To Php/mysql
I have a site running an online continuing education course, it was created as an asp site, using MS Access for the database. I'm attempting to transform or migrate it for use on my main e-commerce site which is hosted by a Yahoo merchant account. They only support PHP, and MySQL. They advise the installation of phpMyAdmin,on their server.I have tried this,but could not see a way to connect to it via ftp,or to create my database externally,I have read the documentaion, and am lost on it as well. I have determined that this is more complicated than I had origionaly thought,as I am getting confused. I run everything using Windows XP home addition, and use Dreamweaver 8,and connect via ftp to the servers.I have MySQL serer 5.0 installed on my PC. I have used asp2php and converted the pages,and have used MySQL migration tool for the database via PHP. NOW I'm lost,and can't decide exactly what to do next. Can anyone give me advice about exactly where to start,and what I need to install, or specific tutorials to read? I feel that I must first get the database established on my server,before attempting to correct any links for the PHP site to utilize it, and uploading them.
View Replies !
Converter: MySQL To Access?
I'm a novice MySQL user, and need a recommendation on what FREE program i could use to convert a MySQL DB to MS Access. I'm using Bullzip's Access to MySQL prog to convert that way, but need a prog to go the other way as well. I would just use a direct connecting feature, but my access to the internet is sporadic right now (I'm in Tanzania, where home internet access is about 1/3 of my wee volunteer salary!), so editing while on-line is not a good solution.
View Replies !
Moving An Access DB To MySQL
I have a client that I am thinking of migrating to MySQL and away from MS Access. I have database skill as far as Oracle Administration and SQL statements but am definitely not knowledgable when it comes to front ends. I know that MS Access has an easy VB WYSIWYG form builder (at least the last time I looked at it which is about 4 years ago) and I am curious as to what is available for building forms for MySQL. Getting their data from MS Access to MySQL will be a snap but I don't know how I would provide them with a graphical front end to allow them to add/delete data. I know WYSIWYG is laughable but I am not a programmer and their needs will be quite small. Probably only 2 or 3 screens needed for manipulation of data.
View Replies !
How To Convert An Access DB To MySQL DB?
I have a client who would like me to convert their Access DB into a mySQL DB for use online with a live search function for their website visitors. I am using a MacBook Pro so I hope there is a tool out there for OSX (Intel). If not, I can also run Windows XP on the same laptop.
View Replies !
Convert Ms Access Db Into MySQL
I have migrated in 2007 from (Ms Access to Oracle) to MySQL. We have webistes that we have developed with php/apache/mysql. We also got the new project now that was developed already, now we need to convert from ASP to PHP & Ms Access to Mysql. I am struggling to import the Ms Access database into MySQL. Can someody help e with the guidlines on how to import msaccess to MySQL?
View Replies !
Grant Access To MySQL
I have grant access to a user: GRANT SELECT ON database.* TO 'webuser'@'%' IDENTIFIED BY 'password' But I can not log in to MySQL (with MySQL monitor). However, I have another user: GRANT SELECT ON database.* TO 'anotheruser'@'localhost' IDENTIFIED BY 'password' This user can log in, does somebody why I can not log in with the first, can there be a problem with the wildcard character: '%'?
View Replies !
Can't Access Legacy Mysql
I bought a used Ubuntu box with $ mysql --version mysql Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i486) using readline 5.2 (It looks like this is the latest version for Ubuntu, and I wonder what I am missing.) It looks like I need a password to get started, and it apparently isn't root+system_passwd: $ mysqladmin -h localhost -u root -p password mysql Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' $ mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y ES) I wonder if a full uninstall/reinstall is called for, presumably 'apt-get remove mysql-server' and 'apt-get install mysql-server', or am I missing something?
View Replies !
MySQL Monitor Access
I am having trouble connecting to the MuSQL monitor via Command prompt, every time i try to open the Monitor i get the following error "ERROR 1045 (28000): Access denied for user `ODBC`@`localhost` (using password: NO) " No i can use phpmyadmin to create a DB and I can access a DB via PHP but it won`t let me open the Monitor in Command Promt.
View Replies !
|
TRACKER
