when i search this table using PHP using simple queries, my CPU utilisation grows exponentially. just 1 search query on this table causes my CPU to spike to 30%... and when i have multiple queries the CPU utilisation grows to 300%..

could somone help me optimise this table please...
(the number of rows in this table is > 100K)

Comment

As for the two others.
1.
Try not to use negations like adult <> 'yes'.
Change them to adult = 'no' instead.
Index usage is much improved.

2.
An "... ORDER BY RAND()" can not use an index. Hence it will always require CPU to order the records. But note that the amount of records that needs sorting is determined from how many that match the WHERE and/or that are left after a GROUP BY statement since sorting is one of the last steps in a query execution.
The only thing you can do to speed this query up is to make sure that you have a sort_buffer_size setting that is large enough to store the temporary table in RAM instead of having to write it to disk.

The reason why mysqld is reporting 29% in the list is that the mysql process is in fact a _lot_ of threads and a rounding error for each thread makes the cpu usage to rise. But that is purely a display problem.
The summarized cpu usage for the system is what you should look at and 7.5% isn't that high.

Comment

Recommend splitting the table up so that there is a main
table with a few commonly accessed fields and a child (dependent)
table that has all the less commonly accessed fields. The goal
is to slim down the number of columns in the main table.

The row size is sooo big for this table that it will be
slow to search as not many rows can be packed into a 16k
block with a large row like this. This means that it will
take a lot of disk accesses to search this table. Making
it skinnier will improve search speed.

You would want to put any fields that are searched for in
the main table, or any fields that are always accessed in
the main table.