GA pilot reported an NMAC in the traffic pattern at PAO when they misidentified the aircraft they were instructed to follow for landing and turned out of sequence.

Narrative

PAO had several aircraft in its normal right-hand pattern Runway 31. I departed PAO with the intent of flying a pattern. Upwind; I was advised to fly a left-hand pattern. Right hand pattern at PAO is 800 ft.; left hand pattern at 1;000 ft.On downwind; Tower advised me I was #4 for landing; to follow #3. I advised Tower I saw a traffic at my 9 o'clock. Based on my communication with the tower; I was under the impression that this was the traffic I was to follow; turned base after extending downwind a bit further. It turns out that what I had identified as the 9 o'clock traffic was the aircraft before the one the tower meant me to follow. On base; I see a traffic diagonally below (just turning final); I immediately turn to the right (pointing downwind); add power & climb. The tower instructs me to continue the downwind; the conflict aircraft; a light single; advised of a near miss; the tower advised that aircraft they may continue. The main issue was that I misinterpreted the aircraft to follow. I thought it was clear based on my communication with the tower; but that conclusion was erroneous. To prevent a recurrence; two suggestions: when a busy controlled tower has both left hand and right-hand traffic; the tower; on a best effort basis; should be encouraged to call base. I understand tower is at times reluctant to do so based on the varying speeds of aircraft; but faster aircraft could still wait an extra few seconds before turning base after receiving instructions based on their experience (or conduct S turns on final which is safer than a direct conflict of traffic turning base from both left hand and right hand traffic). For the pilot; stating 'traffic 9 o'clock in sight' is; with hindsight; not helpful as it may not be clear to the tower whether I'm referring to an aircraft on final (which I did) or possibly an aircraft on right downwind. Better would have been 'traffic 9 o'clock; on final; in sight'. More generally for the industry; better standardized phraseology for acknowledging seeing other aircraft in the pattern; absent of that; it may be best practice not to acknowledge seeing another aircraft; insisting that that the tower call the base.Upon landing; I taxied to parking to reflect on the event rather than do more pattern work as I had originally planned. A contributing factor - while I'm an experienced pilot by many measures; this aircraft is comparatively new to me - hence also the initiative to practice some landings. Nonetheless; it was somewhat of a training environment for me (I was practicing pattern work). As a lesson for myself - and possible guidance for others - is to consider taking another pilot or flight instructor along in such instances even when no Second In-Command (SIC) or CFI is required. I have done that in the past - including not long ago getting to know another aircraft's instruments better. And I suppose that's an important reminder to experienced pilots (notably also those mostly flying instrument approaches) pattern work requires and deserves the same attention as any other aspect of flying.