[Solved] large MySQL db 'backend' runs badly

[evwool]

I have just copied a large database (3 main linked tables with over 28,000 rows per table) from Access into a Base database which is linked to MySQL community server using a 'Native Connection' (?). The database and tables were created in MySQL and then the database opened with Base and the data pasted into it from Access. It's an English Esperanto Dictionary with one table holding the English words, one holding the Esperanto and another putting the correct English Foreign key with the correct Esperanto foreign key. The Individual tables open OK but I've tried to create a query which lets me see the English and Esperanto translations.
Adding just one of those language tables to the query along with the Translation table gave me a query that took over a minute to open, but with all 3 tables, it just crashes my PC. Yet the 3-table query opens almost instantly in Access which is supposed to be weaker than MySql. Is there anything I can do to make MySQL behave?

Edit: Just tried running TblTranslation and TblEnglish Inner Joined in the MySQL command window with the BASE db closed. It took 1 minute 52 seconds to run.

Edit: Just run an update query on the table (1hr 20 mins!). Guess I won't be ditching Access after all

[FJCC]

I can't say what is wrong with you MySQL db, but I regularly use a MySQL db with multiple tables with millions of rows and queries usually run quickly. Can you provide more details about how the db's are set up? Is the indexing set up identically?

[evwool]

I'm using the MySQL Community Server. Could that be what's making the difference? (The database is on a standalone, unnetworked PC).

I'm not sure what you mean by the indexing being identical. Access automatically sets up indexes for primary key fields. Should I be doing that using MySQL's Alter Table command or are Primary Key fields automatically indexed in MySQL? The only real use I make of Indexing in Access is when I want a combination of 2 or more fields to hold unique records.

[FJCC]

I'm sure MySQL constructs an index on the primary key. I mentioned indexes because I remembered this post where changing the indexing made a big difference in performance. On the other hand, it seems to me that 28 000 rows should not be a big task for MySQL under circumstances. In the MySQL query browser you can see the indexes with the command

Code: Select all

SHOW INDEX FROM table_name

[eremmel]

I do not have any experience with MySQL, but in general there are a few more issues that can contribute to issues with queries, despite correct indexes. There is the issue with VARCHAR() type vs NVARCHAR() type. That is VARCHAR in a specific locale and vs NVARCHAR in unicode. Some databases create column types in VARCHAR() by default, but the java connector binds values as NVARCHAR(). This results in data-type promotion. This means that the values from the table/index are promoted from VARCHAR to NVARCHAR so this means that the index can not be used as an index, but has to be fully scanned. The same might be the case when performing join operations and there is a mismatch between PK and FK in respect to VARCHAR -> NVARCHAR.

Some databases store the collation of the data type with VARCHAR, so when you access the database with an other collation conversions might take place as well.

Performance issues are related to time and a process is spending time on the CPU or is waiting on something (IO, other process,...). So it would be nice if you can tell us what your system is doing when it is processing the long running query (CPU or IO). You can use task manager for this, but a much nicer tool is process explorer (procexp.exe) from www.sysinternals.com (now days owened by Microsoft). These tools are a must for those windows users that have some technical courage

I suggest that you show us your table and index definitions and your queries, we might draw some conclusions from there.

[evwool]

I found that the problem was how I'd imported the data. The best method turned out to be simpler (but had its own odditys). I created a database in MySQL giving it the character set utf8 and the Collate of utf8_bin (because I've got accents in some of my text fields).
I opened the MySQL database with Base, to give me a Base-MysQL database. In Access, I turned all my Yes/No fields into Number fields in the Table Design view. Any dates would have to be in the format eg 2009-12-30 and text fields were limited to 250 characters. I opened the Base front end and slid my tables directly from Access into the Base/MySql database. Within the Base Wizard, I right-clicked on my Primary key field and chose Primary Key, I checked that the Wizard chose the correct datatypes for my tables and ignored an error message that appeared about incorrect column values or something. I didn't worry about foreign key fields or autonumbers - you can append to MySQL key fields so it kept the existing numbers of Primary key fields which I had in Access. It took ages to import so don't think you've crashed. When it finished, in the MySQL window, I used Alter Table to set my Primary Key fields to Autoincrement and, to my surprise, it was happy to do so (Access won't let you set a field to Autoincrement/Autonumber if it contains data). The oddity is that the accented text displays and filters incorrectly in MySql but is just fine in my Base front end. I guess I can call this not so much solved as evaded.