2 questions

zendboy

do you have a tool to convert access db to mysql db?

2.
I have a database, big Mysql db
it has only one table but this table contains more then 10 million records,
when the user search in the db it takes a lot of time and my server load get up to 10!

i used the simple code to connect and get rows from the db,
what can i do? how can i improve it? that it will not take so much time and resources

Benbread

why dont you have a db table (or row for that matter) for each letter (abcdefg....) that would speed up your searching, as you would just have to search a table taht is cut down by about 26 times...

zendboy

it's a database of phones
and you can search by name and last name, by name and city, by last name and city, by name lastname and city, by name and father, etc....

so i can't do it

Benbread

thats a shame, errrrmmmm well im afriad i cant help you much... why dont you get people to refine there search with say first name of city etc

drawmack

please post your structure including indexes. Mysql should be able to handle that size table, my gues is that you've indexed it improperly.

zendboy

Originally posted by drawmack
please post your structure including indexes. Mysql should be able to handle that size table, my gues is that you've indexed it improperly.

here is the structure

Benbread

why havent you set id to auto_increment? also tell mysql to optimise the table

drawmack

db rule of thumb 1) If you're going to use the field in an order by, group by or where clause it should be indexed. Try running this query:

ALTER TABLE <table name> ADD INDEX <field name> (<field name>);

for each field that the user can sort by or trim by and you'll probably notice a marked improvement in query time.

zendboy

thank you!!!!!
" Query took 0.00038 sec "
!!!
it took 12 secs at the first time!!!

drawmack

okay now run these two queries

REPAIR TABLE <table name>
OPTIMIZE TABLE <table name>

and you're table will be as efficient as possible.

Benbread

who is the thank you to?

zendboy

to both of you Benbread and drawmack,
i have been looking for the solution in many sites and manuals but didnt find , i didnt think that the problem could be the indexing..

Benbread

thats ok, but remember, if you are constanty getting new information, you need to index it regulary otherwise you will notice it start to slow down again..

drawmack

not really now that he's created the indexes mysql should mamange them for him. Though optimizing and repairing on a regular basis is always a good idea, I usually set up crons for that.

His major problem was that he was using non-indexed fields in the where clause.

Benbread

Strange, im sure that i said optimising... lol, anyway, yeah do that and you should be dancing.