MySQL index implementation on MyISAM tables

[Harald Tijink]

Hi,

Can someone point to a location where the implementation of indexes on MyISAM tables 
is explained? I'm trying to understand exactly (not roughly) how the indexes are 
created and used.

Harald

INSERT fails - return primary key?

[Harald Tijink]

Hi,

Say I have a table with 2 columns. The first is the primary key (int(11), unique, 
auto-increment). The second column is a varchar(20) (also unique and indexed). This is 
part of a search engine. 

Whenever a new document is indexed then for each word I have to do 2 queries: look up 
in the table if the word exists, if yes - use key, if no - insert and use key. I 
want to be able to combine these actions into one INSERT query which returns the 
primary key whenever a word already exists, or insert the word and then returns the 
(newly created) primary key.

Anyone any idea if this is possible? If yes, how this is possible?

Cheers,

Harald

Re: Performance Problems

[Harald Tijink]

Do you use indexes?

See http://www.mysql.com/doc/en/CREATE_INDEX.html.

In my system a retrieval from a 24 million records table (3 columns) with a
result of 25 records only took 0.09 sec and 24 million records table with 5
columns 0.25 sec

Harald

- Original Message - 
From: Schonder, Matthias [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, September 18, 2003 11:24 AM
Subject: Performance Problems


 Hei :)

 I have an extreme performance problem with a MySQL-DB.
 The database consists of 21 tables where all except three are storing only
a
 few records. Two have about 150.000 records but they are only used
 temporary. The main table is rather huge, it has 90 columns and now after
 three month it has 500.000 records... but in the end it has to store data
of
 36 month.
 But since the table has grown to over 350.000 records I ran into massive
 performance problems. Querying for one record (Example: SELECT sendnr FROM
 pool where sendnr = 111073101180) takes 8 seconds via command line!
 The table is indexed and i put the my-huge.cnf into /etc/ as my.cnf

 How can I get more performance out of the mysql?
 The server which currently only hosts this database and is running
apache1.3
 with php4 for providing results via intranet.
 The OS is FreeBSD 5.1.
 We are running two servers with the same enviroment
 One is a DualXeon 2.4ghz (with HT), 3GB RAM, 105GB HD (RAID5)
 the other one is a P4 with 2.4ghz, 1GB RAM and 120GB HD

 The Dual is the Productionserver, the P4 the developement- and test
server.
 The querey takes that long on BOTH machines so it seems clear the DB
itself
 is causing the performance problem.

 So anyone can help? This is really urgend and will save my life :)

 Big thanks in advance.

 Pacem,

 Matthias Schonder

 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: combining fields in select

[Harald Tijink]

Piko,

Please see http://www.mysql.com/doc/en/String_functions.html for more
information about String functions in the SELECT. The function you're
looking for is CONCAT or CONCAT_WS.

Harald

- Original Message - 
From: Michael Piko [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, September 18, 2003 11:55 AM
Subject: combining fields in select


 I am trying to select the firstname and surname fields from a table.

 select firstname + surname fullname from people;

 This does work in other databases but does not seem to work here.

 Am I doing something wrong or is there a different way to achieve this
with mysql?

 Michael



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]