Table scanning?

[mikeknox]

Hi

I'm working on a C program to load some data into a MySQL database - unfortunately I'm getting some incredibly slow results which I can't explain.

The table has two indexes
1) ip, mac_address
2) mac_address

Queries are updates/inserts/selects - normally on mac_address+ip (and a couple of other columns)

or on =mac_address and != ip
or on !=mac_address and = ip

Explain plans indicate that either index1 or index2 will be used.

However status shows incredibly high hander_read_rnd_next values - so I think I must be table scanning. How do I identify whats happening?

The machine is a Dual proc Compaq 360DLT with 72 GB and 2GB RAM. Using MySQL 3-23.51.

Stats are from a run of 20,000 records....

working on a table of approx 180,000

(In attachment)

Any help will be gratefully recieved......

[mikeknox]

Just for others :- although I've still got a high rnd_next values - I've managed to improve performance by extending the 1st index by adding starttime (it's also unique) and by increasing the second index to include ip and startt.

Unfortunately I still can't identify the offending query - the minimum slow query time appears to be 1 sec - I suspect I need to set it down to 0.3 to identify the table scanning query.

[mikeknox]

Cracked what the problem is - don't know why

Now have 2 unique indexes

index 1
ip
mac_addess
openflag
startt

index 2
mac_address
ip
openflag
startt

two update queries
UPDATE QUERY 1
update set column...
where ip='x' and mac_address != 'y' and openflag=1

UPDATE QUERY 2
update set column.....
where mac_address='y' and ip !='x' and openflag=1

Using the EXPLAIN Select on the where clauses both correctly identify the index as being used.

Update 1 works fine
Update 2 table scans and obviously does use the index.

:confused: