Member Avatar for desiguru

In my mysql table there are following values:

ID (unique)(Auto incre.)
INI
URL
Name
DeprSize
Dept. Location
Knownsince
MeetingNotes

The php queries are

1) SELECT * FROM `dep1` WHERE `INI`
2) SELECT * FROM `dep1` ORDER BY `dep1` .`Name` DESC


And the sql indexes are:
----------------------------------------------------------------------------
Keyname Type Cardinality Field
----------------------------------------------------------------------------
PRIMARY PRIMARY 13 FileINI
id UNIQUE 13 FileINI
Name UNIQUE 13 Name
FileINI_2 INDEX None FileINI

----------------------------------------------------------------------------

I have about 45 tables just like this one and about 1200 rows in them


Now here is the problem:

The data in MySQL runtime information are really bad (They are all in red so i assume that's not a good thing)
--------------------------------------------------------------------------------

Handler_read_rnd 15 M
Handler_read_rnd_next 202 M
Created_tmp_disk_tables 38
Select_full_join 48
Opened_tables 1 k
Table_locks_waited 151
--------------------------------------------------------------------------------


Can someone please help me on how i can optimize the queries so that the valuies like Handler_read_rnd_next are not too high all the time.

Thank you!

  • 2 Contributors
  • 1 Reply
  • 104 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

Why do you have 3 indexes on the INI field (primary, unique and index) ?

Also add an index on (dep1 and name)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.