MySQL Bugs: #25608: CREATE DATABASE create_specification enhancement ...

Bug #25608	CREATE DATABASE create_specification enhancement ...
Submitted:	13 Jan 2007 14:32	Modified:	20 Feb 2007 10:57
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
I am having big performance issues with a machine with several thousands of relatively small databases... (~10 table - ~10 fields per table)

It would be very usefull to enhance the CREATE DATABASE syntax, in order to be able to specify the directory location where the databse is to be created.
An application can then manage it according its needs...

a special internal table can be use for this with at least 2 fields :
- the db name (primary key)
- the db path

(perhaps within the mysql db)

database selection can then look at this table to open the database
if present within the table, use it, otherwise, use the default data_dir

it will be transparent for all existing databases, and people can use new behaviour according to their needs, and migrate step by step...

it will allow to easyly distribute databases among filesystems.
it will speedup the "information_schema" access by reducing directory traversal...

How to repeat:
-

Thank you for a feature request. Sorry, but I see no need to implement a feature like this when it is possible to use symbolic links (read http://dev.mysql.com/doc/refman/5.1/en/symbolic-links.html) and partitioning (read http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html and links from that page).

sorry, perhaps i did not explained myself correctly(I am french and my english is not so good)
the feature is needed to solve a performance problem:
when you have several thousands of databases with several thousands of symbolic links in the /var/lib/mysql directory, it takes a very very very long time to open phpmyadmin, due to the directory traversal and retrieval from symbolic links.
if instead there was a table containing some kind of symbolic link,
(database_ name => directory_location) the traversal would be immediate...

I hope that this explanation is bette than the previous
Best regards
Pascal

Now it's more clear. The problem is performance with a lot of databases when symbolic links are used. Maybe, phpmyadmin should just no try to access all the databases upon opening... Anyway, I think, partitioning in 5.1, with ability to store data and indexes for partitions of individual tables in separate directories, may be a valuable alternative solutions to symbolic links in this case. Please, check.